QUESTION :
I have a cell with a number (formatted as “52,9” in my locale) and I want to format it into a String in another cell with a specific decimal separator (“52.9”). I don’t want to change my locale or anything, I want it to work the same no matter what locale is used. I could of course do some complex formula to strip out the whole number and the decimals and stitch them together again, but my question is, is there a function that does this?
I’m using the latest version of Excel 365.
ANSWER :
Yes, that’s pretty easy:
=Substitute("25,84";",";".")
This will result in a string like this:
25,84 -> 25.84
Obtain the decimal separator char in a separate cell. Use it for to replace.
Example.
-
Put the 1,1 number into a cell A1. It is a reference standard.
-
Put the formula =MID($A$1,2,1) to B1. It is current decimal separator.
-
Put the value which you want to convert to a string with specific decimal separator (52,9) to A2.
-
Put the formula =SUBSTITUTE(A2,$B$1,”.”) to B2. You obtain the result you need.
Of course, this can be combined into one complex formula =SUBSTITUTE(A2,MID($A$1,2,1),”.”) freeing B1 cell – it is safe when the amount of numbers which must be converted is low.
The reference standard and current decimal separator formula can be placed to any static cells (for example, in hidden 1st row or on service/hidden worksheet, or maybe there is some suitable static data in your workbook which can be used as a reference standard…).