One quick way to convert decimals in Excel

I frequently use the “text to columns” feature in MS Office 2007 and higher. Usually I paste csv or other delimited data in the first column, and use the Text to Column wizard to convert it to individual columns. It’s also very useful for converting decimals. Regional settings and the use of decimals can be a real pain, especially when importing data from various sources. Usually the solution is to change decimal settings in Excel, or even in your operating system (regional settings). I do not understand why there is no feature in Office to interpret a field as a number regardless of the decimal separator, but fortunately there is a workaround.

The case here is that the number value is not recognized correctly, for instance a field contains 24.67 and Excel thinks it’s not a number.

The solution is to apply the Text to columns to the erroneous column again. See what happens.

You can find the button here (I use Office for Mac, so it might look different for you):

In the example below, the point is not recognized as a decimal point. Apparently, Excel accepts only comma’s here (might be the other way around in your case).

 Select the column first.

Click the Text to Columns button, then select the “delimited” option, click next. Click next again. You’ll see this page:

Now click “Advanced…”.

Hah, I may choose a decimal and thousands separator. Change the decimal to a dot, and the thousands separator to a comma. Or again, the other way around. The trick is that you tell Excel here which character is a decimal, and not what it should be. A bit confusing, eh.

Click OK to leave the wizard. And, tad-aah:

These look like real numbers to me. Patrick vs. Excel: 1-0.

convert decimals in Excel

14 Reacties

  1. was struggling with the deciaml formats from an export and pivot table didn´t work
    absolutely helpful – saved my day

  2. the only really useful answer to the dot/comma problem.
    thank you very very much.

  3. You can also select the colums and use ctr+F, replace comma by dot:)

  4. Thanks a lot! I have spent hours converting them manually over the past…

  5. Thanks! Just what i was looking for! Good, simple tutorial! 🙂

Laat een reactie achter

Het e-mailadres wordt niet gepubliceerd.

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

By using this site you acknowledge the use of cookies (which are mostly harmless, btw) More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below you are agreeing to these settings.

Close