Two different separators in Excel 97? 2

[owens2024]

Hi -

I have an Excel 97 spreadsheet that lists measurements in both English and metric. I would like to replace the '.' with a ',' for decimal in the metric values? Does anyone know how to do this without re-entering all the data as text?

Thanks -

Lee

 

[ETID]

this formula will take a 2 decimal place value and return a text representation as you described.


if cell A1=10.34 then the formula will return 10,34

=INT(A1)&","&RIGHT(TEXT(MOD(A1,INT(A1)),".00"),LEN(TEXT(MOD(A1,INT(A1)),".00"))-1)

 

[ETID]

Note this will work for 3 dec place values

=INT(A1)&","&RIGHT(TEXT(MOD(A1,INT(A1)),".000"),LEN(TEXT(MOD(A1,INT(A1)),".00"))-0)


and this for 1 decimal place values

=INT(A1)&","&RIGHT(TEXT(MOD(A1,INT(A1)),".0"),LEN(TEXT(MOD(A1,INT(A1)),".00"))-2)


...modify the ".00" and -1 in the formula to suite.


fill down

 

[bluedragon2]

This will work for any number of decimal places:

=LEFT(A1,FIND(".",A1)-1)&","&RIGHT(A1,LEN(A1)-FIND(".",A1))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[owens2024]

Thanks for your help!

- Lee