I converted this text file to a comma delimted file, then I set this coulumn of dollar amount to account type, but I am having this issue of number being indented to the left as shown in the picture below. Any Idea how to fix this? I tried setting different format type, decreasing and decreasing indent but to no avail. It looks like if I manually delete the spaces it fixes, any way to do this to the entire column, its about 800 cells, so it would take forever to do manually. Thank you.
2 Answers
It looks like on import of the text file, Excel removed the comma separator from your numbers greater than one thousand.
So to Excel those look like the text representation of what you see as a number so 10 000.00 Excel sees as the characters "10" + " " + "000.00"
Use the substitute function =NUMBERVALUE(SUBSTITUTE(L1," ",""))
to remove the spaces and convert the text to a number format.
Then just do a Copy > Paste Value and you will have a numerical column you can work with.
-
Not sure what I am doing wrong, but I cannot get this to work. Excel is not recognizing NUMBERVALUE function? I am on Excel 2010. Sep 4, 2015 at 17:58
-
Ok I just figured it! I ended up this formula, =N(VALUE(SUBSTITUTE(L3," ",""))) Sep 4, 2015 at 18:22
It is the format selected by Excel when importing the data. More specifically, it is most likely the accounting format.
To fix, select a different format from the home tab or right click on the cell and select properties. Select custom format and change to what you want.
-
Hmm, can't seem to get the comma, or remove the space between numbers, any idea? Sep 4, 2015 at 18:03