Space as thousand separator in a csv file.

I have a CSV file (for example that contains the number 10 582 which uses a space as the thousand separator so there is white space between the 0 and 5).  When I open that in Excel if I try to use a formula for example =A1*2 it returns #VALUE because it sees that number as text.  My computer regional settings have space as the digit grouping and the in the Excel options I choose to use the system separators.  How can I get the number in the csv file to be seen as a number and not text?
 

Question Info


Last updated November 27, 2018 Views 2,579 Applies to:
Answer
Answer

Rename the file to have extension TXT

When you open it in Excel, the Data | Text to Column dialog should automatically appear

Use Delimited by comma, then open the Advanced setting and specify that the thousand separator is a space

I did a small experiment and this worked for me. best wishes

http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Try the following.

Format the column to your desired number format. (Will not appear to do anything initially.)

Select the column then open Find/Replace.

Insert a space in the "Find what" field and nothing in the "Replace with" field and then click Replace all. That should remove the space (which is actually text; not just part of a number format) and data should be in your desired number format.

However, if the data remains left justified and does not display in your desired number format then select a blank cell somewhere on the worksheet and enter 1. (Numeral one)

Copy the cell and then select the data in the numeric column and then Paste Special. Select the Multiply option and click OK.

 

Regards,

OssieMac

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.