3

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.

enter image description here

2 Answers 2

2

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.

2
  • 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.
    – arealhobo
    Sep 4, 2015 at 17:58
  • Ok I just figured it! I ended up this formula, =N(VALUE(SUBSTITUTE(L3," ","")))
    – arealhobo
    Sep 4, 2015 at 18:22
0

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.

Custom format in Excel

1
  • Hmm, can't seem to get the comma, or remove the space between numbers, any idea?
    – arealhobo
    Sep 4, 2015 at 18:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .