Opening CSV file, numbers are not recognized as such

Question

Someone posted this question... 

https://answers.microsoft.com/en-us/msoffice/forum/all/opening-csv-file-numbers-are-not-recognized-as/25615eb4-6e76-4697-9d17-75b99ecb8d6b

and (as typical of Microsoft products)  The answer DOES NOT WORK.

When I open a *.txt or *.csv file, OR when I import the data from the file using the "Get External Data" option, it ARBITRARILY decides that MOST of the data are labels.  But not all.  JUST MOST, intended to ANNOY THE USER...  EVERY LAST BIT OF THE DATA IS NUMERIC.

And once it's in there, no option in the universe will convert it from text to numeric.

I have now wasted more time trying to solve this problem that being productive.

Here is a screen shot... where it is OBVIOUS that the "left justified" are text, and the "right justified" are numbers.  However there is NO decoration on the "text cells".  Why is column A and K numeric, but the others aren't???

Here, pasted, is a segment of the text file that has values separated by commas.

 4,0106 ,0.094 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.109 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.094 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.094 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.109 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.109 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.078 ,0.000 ,0.078 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.078 ,0.000 ,0.078 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.109 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.109 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.094 ,0.000 ,0.078 ,0.000 ,0.000 ,0.000 ,0.000 ,0.016 ,0.000 

 4,0106 ,0.109 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.016 

 4,0106 ,0.109 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.016 ,0.000 

 4,0106 ,0.094 ,0.000 ,0.094 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.078 ,0.000 ,0.078 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.063 ,0.000 ,0.063 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.062 ,0.000 ,0.062 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.078 ,0.000 ,0.078 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

 4,0106 ,0.062 ,0.000 ,0.062 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 ,0.000 

There is NOTHING in this data to make me, or any normal person this this is text.  And yet Excel does, and you CAN'T CONVINCE IT OTHERWISE!

Answer 1

From the above screenshot, it seems there is a space before every comma except the first one in each row  which is the reason why excel considers all other numbers as text except 4.

One way to resolve this can be ...Open your csv in notepad, do a find-replace of a space with nothing and then open it in excel - all numbers will come as numbers.

Answer 2

Good catch.  Thanks, I appreciate it.

But I've never had an issue with whitespace before.

And with 8,000 lines, and the occasional text that needs a space here or there really makes this annoying.

So now I get to regenerate this data (fortunately for me, I can).

MS's penchant to constantly change (and make worse) their products really annoys me.

(I can't even get these products to do a clean client re-paint to their screens.  Something that used to be simple and easy to master 30+ years ago.  Now I have half re-drawn emails, word docs, VS windows... because they can't understand how to sequence a simple "WM_FOCUS" message)