# Microsoft Office Application Help - Excel Help forum > Excel General >  >  [SOLVED] converting comma to decimal point

## greenhouse

I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.

How do I change the comas into decimal points so that they are readable?

----------


## shg

Find and replace comma with decimal?

----------


## DonkeyOte

If you have numbers in 1000's ie: 2.340,05 then you should run Edit -> Replace twice, first replace . with nothing then run again but this time as shg says replace , with .

----------


## greenhouse

Thanks guys that was really helpful

----------


## d0ckers

> If you have numbers in 1000's ie: 2.340,05 then you should run Edit -> Replace twice, first replace . with nothing then run again but this time as shg says replace , with .



I don't understand how to apply this Edit. Where is this function in Excel?

----------


## DonkeyOte

d0ckers, welcome to the board, however, please note:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## gsticklee

I found this thread while trying to solve the problem myself. I have posted my solution below in case anyone else needs to know how to do it.

The alternative to using find and replace would be to use nested substitute functions.

To convert from 22.222,00 in cell C15 to 22222.00, you could use 

_=VALUE(SUBSTITUTE(SUBSTITUTE(C15,".",""),",","."))_

The inner substitute _SUBSTITUTE(C15,".","")_  replaces the dot thousand divider with a blank.

The outer substitute replaces the comma with a dot, which is the decimal point in the uk.

The VALUE() function converts the text into a number.

----------


## eyong123

hey bro your value and substitute formula works perfectly well...thanks a lot

----------


## dobedani

I'm using Office 2016 and the internal find / replace function of Excel did not work for me. I found the following solution. Let's suppose those data are in a column. Insert a new column next to it. Select the data from the column and copy them - e.g. by using Ctrl-C. Open a text editor and paste the data - e.g. using Ctrl-V. Now use the find / replace function of the editor to do the conversion. Then copy all the data again and paste them back into the new column. If you want, you can now check the conversion. Finally delete the column with the original data.

----------

