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?
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
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.
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.