I imported some data and it is not formatting correctly for numbers

Occasional Visitor

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 function but still col lists 12 digits.  Need help to clean up the data

2 Replies
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.


Your problem statement is unclear to me.  I think it would be clear if you attached a "spreadsheet" file that demonstrates the problem.


(Not clear if you are talking about an Excel file or CSV file, which Win displays with an Excel icon.)


First, what is the "col" function?  Ideally, provide a URL to a support page that describes the function.  I would do a google search for ``excel col support`` without quotes; I would use a URL in the support.microsoft.com domain.


Second, you say that it "appears" as 10 digits, but the function "lists" 12 digits.  What do you mean by "appears" and "lists"?  That is, specifically where does it "appear" as 10 digits; for example, when the cell is displayed (what format?!); or in the Formula Bar?  And in where does the function "list" 12 digits; for example, as the result of a formula (show us the formula!)?


And what is the form of the number? Both 1234567891 and 12.34567891 have "10 digits".  But they are very different forms of numbers (integers vs. non-integers). 


The first might be "corrected" by explicitly rounding to 0 decimal places.  The second might be "corrected" by explicitly rounding to 8 decimal places.


Alternatively, I wonder if you mean a number with 10 decimal places; for example, 123.1234567891, which is a __13__ digit number.


(But that assumes the data is truly numeric, in first place.  It seems that it is, if it displays more digits in one context than it does in another context.)