Forum Discussion
I imported some data and it is not formatting correctly for numbers
I have some data in a spreadsheet that appears as a number with 10 digets, when I do the col function it is listing as 12 digets. I have tried to format as a number and also tried the text to column...
You may have some non-breaking spaces in that column. So use Ctrl+H to replace them with nothing. To do so, select the whole column and press Ctrl+H to open Replace Window, In Find what: box hold down the Alt key and press 0160 from the numeric keypad and then replace all of them with nothing in the Replace with box. See if this resolves your issue.
Or you may also use Substitute Function in a separate column to substitute CHAR(160) with a blank string "" and then copy the formula cells and paste them back to the original column as Values.
Or you may also use Substitute Function in a separate column to substitute CHAR(160) with a blank string "" and then copy the formula cells and paste them back to the original column as Values.