report

Excel Number Format Problem

Fix number format in excel

A common and frustrating problem when working in Excel is when you encounter a spreadsheet which has number formats that are not recognised as numbers. Usually this occurs where the columns are being read as text formats and cannot be converted to number formats. This is a common problem when numbers are in European number format with the comma “,” being used as a decimal separator and a full stop used as a thousands seperator .


This hub explains how to fix this number formatting problem in Excel and reformat numbers into a format which does work.


The Problem – Numbers are not recognised by excel

The below screenshot shows the problem. Column C is in a European number format which is not recognized by U.S. versions of Excel. No matter how I format column C, I can’t get it into a format which is recognized as numbers. If I click on any of these cells and try to reformat them as numbers in excel it does not work, the cells are in a format which cannot be recognized by excel as numbers.


Column C is in a European number format. This is not recognised as numbers by my version of excel
Column C is in a European number format. This is not recognised as numbers by my version of excel

Step 1: Highlight column and remove the thousands separator

The first thing we want to do is remove the decimal point ‘.’ as a thousand separator. To do this highlight the column with the numbers and click on find-replace. Find the decimal ‘.’ and replace it with nothing (i.e. don’t write any character in the replace box).

When we have performed this find and replace it will remove the . from the numbers and we have a number format without the . thousands seperator. This fixes half of the excel number problem.


Use the find and replace function to find the thousands decimal and remove it.
Use the find and replace function to find the thousands decimal and remove it.

Step 2: Remove the comma (,) as the decimal separator

European number formats use a comma (,) for a decimal place. This is not recognised by the US version of excel. What we need to do is remove the comma and replace it with a decimal place (.) To do this we use the find and replace function again to highlight column C and find the comma, and replace it with a full stop (.)

Once we have completed this step the numbers will now show a . instead of a comma , for the decimal seperator.

After the find and replace function we can see the . has been removed.
After the find and replace function we can see the . has been removed.
Search on the , decimal seperator and replace it with a .
Search on the , decimal seperator and replace it with a .
Once we have run the find and replace function we now have a . as a decimal seperator which is recognised by excel.
Once we have run the find and replace function we now have a . as a decimal seperator which is recognised by excel.

Excel Number Problem Fixed

As we can now see, after using find and replace to remove the problematic thousands and decimal separators we have numbers in a format which is recognised by excel and can be summed.

Column C is now readable as numbers.
Column C is now readable as numbers.

More by this Author


Comments 2 comments

monicamelendez profile image

monicamelendez 4 years ago from Salt Lake City

I'm liking this series on Excel fixes. I've been trying to learn as much as I can about Excel. Thanks again!


Theeyeballkid profile image

Theeyeballkid 4 years ago Author

No problem, thanks for reading, I hope it was useful.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article

    Menu

    Explore