Import CSV with unconventional number format

Question

Level 1

4 points

Import CSV with unconventional number format

Dear Forum Members,

I have a CSV file with several columns. One of them contains numbers with a space as a thousand separator. I can't find the way to have Numbers interpret them as numbers, and I can't even convert them within Numbers to numbers. They are understood by Numbers as text.

I have tried the following:

When I open the CSV file, I adjust the import settings via the decimal and thousand separator. Does not work for the thousand separator.
I tried Find and Replace but I can't limit it to one column and there are other columns with text, therefore this is not a good solution. I could hide all other columns and then do the Replace but that can't be the solution; I want to do this regularly for updated CSV files.
Create a new column where the space is substituted by nothing. I have tried it using SUBSTITUTE("-2 480,85";" ";"";1), which works if I input the value by hand but does not work if I refer to the cell with this value.
Create a custom number format with "# ###,##" as template. Did not work.

I sort of know Excel but I am not an expert at Numbers. Thank you.

Posted on Oct 8, 2022 12:19 PM

Reply

Answer 1

Top-ranking reply

ye674 Author

Level 1

4 points

Oct 8, 2022 11:28 PM in response to ye674

Thanks all for the input. Even though I had read about Barry's tip before posting the question here, I have only tried it now and surprisingly it did not work. That brought me to check whether that character was actually a space character. And no. It is a non-breakable space character. That is the reason for all the failures. (For clarity: space has ASCII code 32 and non-breakable space has ASCII code 160.)

Now that I know this, the simple Find&Replace works like a charm. Instead of searching for a space character, I'm searching for the character that I copy-pasted from one of the cells. Only this column contains this character, therefore Replace All is perfect. Thanks again.

Reply

Answer 2

Barry

Level 9

60,011 points

Oct 8, 2022 02:08 PM in response to ye674

Select and copy the values in the 'number' column,
Paste those values into a single column table in a separate Numbers document).
use Find and Replace on that table to replace the space(s) in the numbers with the thousands separator you want to use.
Select the column and set its data format to Number.
Copy the results, and paste them back into the 'number' column of the imported table.

In this example, the space separated values in the table on the left were selected and copied, then pasted into a single column on a table (also Table 2) in a second document. The table in the second document contains no text items other than the pasted 'numbers' that are in the unrecognized 'number' format and, as text values are automatically aligned to the left.

With the numbers pasted into a separate document, it was safe to select the column, press command F to open the find and replace pane, set the Find value to a space character and the Replace with value to a comma, then click Replace All to do the conversion.

The converted numbers originally kept the same alignment as they had when pasted into the cells. To change that:

With the column selected:
click the format brush.
Set the Data Format to Number, and click the Thousands checkbox if the command are not displayed.

With the cells still selected, press command-C to copy.

On the original table, select the top cell (B2) from which the data was copied, then press command-V to paste the converted values into the cells.

If they do not automatically align right, set the data format of these cells to Number.

Regards,

Barry

Reply

Answer 3

Ralf-F

Level 4

2,735 points

Oct 8, 2022 02:26 PM in response to ye674

Please try it without the last 1, SUBSTITUTE("-2 480,85";" ";"";1)

This is working for me: VALUE(SUBSTITUTE(A5," ","")) for your region it would be VALUE(SUBSTITUTE(A5;" ";""))

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

Reply

Answer 4

ye674 Author

Level 1

4 points

Oct 8, 2022 11:06 PM in response to Ralf-F

Thanks, Ralf-F. I tried it and this is not the solution. It doesn't work. I have the following function and then error message. The space is not removed when the cell is referred to. I have Austria as region, and here semicolon should be the formula separator.

Reply

Answer 5

Badunit

Level 7

29,910 points

Oct 8, 2022 05:20 PM in response to ye674

Here is another way:

After importing, change the region of the file to one that uses that number format (France for example), format the column as Number and check the box for the thousands separator if you want it, then change the region back to what it was (mine is System-English). It will change the number format to match your region

File->Advanced->Language and Region

Reply

Answer 6

ye674 Author

Level 1

4 points

Oct 8, 2022 11:10 PM in response to Barry

Thanks Barry. I understand that this is a solution for one file but the computer and Numbers exist exactly for the reason that we should not be doing such manual and error prone manoeuvres, in my opinion at least.

Reply

Answer 7

Barry

Level 9

60,011 points

Oct 8, 2022 11:49 PM in response to ye674

Congratulations on your discovery. Perseverance often pays off!

A non-breaking space does make sense in that context—it prevents the number from being split into two parts at the space.

That shouldn't be an issue in a fixed width cell, as it would likely be made wide enough to contain the largest (read 'longest') expected number. It would be useful with numbers in a text string on a page though.

Regards,

Barry

Reply