Forum Discussion
Dealing with various data formats in the same column
Okay I have solved the issue myself. I had to use VALUE() function inside the FORMAT() function like this:
FORMAT( VALUE([Data]),"0.00%")
Super UserAnonymous , You have try like
Switch( True(),
[Desired Format] = "Percent",FORMAT([Data],"0.00%")
[Desired Format] = "Number",FORMAT([Data],"##,##0.00")
//other
)
Formats - refer here https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications
amitchandak The problem is FORMAT function does not work for me. I tried without any IF or SWITCH , just
Data Formatted=FORMAT([Data],"0.00%")
But I still get the same format as original, like:
Data Desired Format Data Formatted
12.23232414 Number 12.23232414
0.121000000 Percent 0.121000000
abcdefg Text abcdefg
Okay I have solved the issue myself. I had to use VALUE() function inside the FORMAT() function like this:
FORMAT( VALUE([Data]),"0.00%")