Problems with Numbers, please help.

[nj-morris]

So here's the issue: I've been using Numbers on my MacBook recently to copy strings of text that I get from files.

Note, I find it very useful that the app is able to sort a line of text by putting everything between commas in separate boxes, e.g. when 111,222,333 is copied onto a spreadsheet, 111 is in one box, 222 in the next, 333 in the third. I haven't been able to find any other software to do this. I've used Google Sheets, no luck, Excel on my iPad without being able to copy anything at all.

But here's the main problem. Sometimes, and this happens particularly with a number, the text won't paste onto the spreadsheet exactly as it was copied, because of the way Numbers formats numbers. Here are a few examples:

010010110 comes out as 10010110
42003E050 comes out as 4.2003E+54
39157157AED comes out as AED39157157.00

It's extremely annoying. These lines are supposed to be identification numbers, not physical quantities. Even when I tell numbers to format the whole table as TEXT before copying the file, it still comes out the same, just on the other side of the cell, because, you know, it's formatted as text, but for some goddamn reason it still doesn't treat it as text.

Can somebody please tell me how to stop this, and make the numbers paste onto the spreadsheet AS COPIED?

 

[adrianlondon]

Change the Data Format just of the cells you're going to past into, to Text. Then paste. It worked for me on a quick cut&paste.

 

[sracer]

So here's the issue: I've been using Numbers on my MacBook recently to copy strings of text that I get from files.

Note, I find it very useful that the app is able to sort a line of text by putting everything between commas in separate boxes, e.g. when 111,222,333 is copied onto a spreadsheet, 111 is in one box, 222 in the next, 333 in the third. I haven't been able to find any other software to do this. I've used Google Sheets, no luck, Excel on my iPad without being able to copy anything at all.

But here's the main problem. Sometimes, and this happens particularly with a number, the text won't paste onto the spreadsheet exactly as it was copied, because of the way Numbers formats numbers. Here are a few examples:

010010110 comes out as 10010110
42003E050 comes out as 4.2003E+54
39157157AED comes out as AED39157157.00

It's extremely annoying. These lines are supposed to be identification numbers, not physical quantities. Even when I tell numbers to format the whole table as TEXT before copying the file, it still comes out the same, just on the other side of the cell, because, you know, it's formatted as text, but for some goddamn reason it still doesn't treat it as text.

Can somebody please tell me how to stop this, and make the numbers paste onto the spreadsheet AS COPIED?

In order to preserve the data as copied, you'll need to first set the cells that will be pasted into as "Text". (on right hand properties inspector: Cell -> data format) If you do that first, when you paste those numbers, they'll be preserved.

edit: ha! @adrianlondon beat me to it by 2 minutes.

 

[nj-morris]

Change the Data Format just of the cells you're going to past into, to Text. Then paste. It worked for me on a quick cut&paste.

In order to preserve the data as copied, you'll need to first set the cells that will be pasted into as "Text". (on right hand properties inspector: Cell -> data format) If you do that first, when you paste those numbers, they'll be preserved.

edit: ha! @adrianlondon beat me to it by 2 minutes.

Yeah I've tried that:

Even when I tell numbers to format the whole table as TEXT before copying the file, it still comes out the same, just on the other side of the cell, because, you know, it's formatted as text, but for some goddamn reason it still doesn't treat it as text.

 

[adrianlondon]

Maybe try selecting a load of cells rather than the entire table, and/or cutting and pasting rather than "copying the file"? I'm not sure what that means. If it's a small file feel free to upload it here and I can have a play.

 

[sracer]

Yeah I've tried that:

Then you are experiencing something else. I've tried single cells and a group of cells and it works as we've described.

What happens when you change the format of a single cell and copy and paste a single value into that cell?

 

[nj-morris]

Then you are experiencing something else. I've tried single cells and a group of cells and it works as we've described.

What happens when you change the format of a single cell and copy and paste a single value into that cell?

Ok sorry for taking so long to reply, but here is what I got when I tried to copy and paste a single cell into Numbers. The text was this: 0800101108

And it came out like this, when copying the text into cell 17A, formatted as text, as shown:


So no problems here. The 0 at the start wasn't removed. So I tried to copy and paste a full row into Numbers, and the text was this: 0800101108,7,Adams 108,Adams,1354,503,10,805,11,6,19,250,148,90

I copied it into the same cell, with the same format, and this came out:


This is undesirable, as I wanted everything between the commas in separate boxes. So I tried to copy and paste multiple rows into the cell. The rows that I used were these:

0800101108,7,Adams 108,Adams,1354,503,10,805,11,6,19,250,148,90
0800101109,7,Adams 109,Adams,2936,930,72,1874,9,24,27,515,300,193
0800101098,7,Adams 98,Adams,3135,1791,60,1123,61,7,93,1117,666,431

And they came out:

This is what I wanted before, because the boxes were separated, but as you can see, the boxes in column A are formatted differently, even though I set them as text. The 0800101108, for instance, came out as 800101108. Apparently this only happens when multiple rows of text are copied and pasted into the spreadsheet.

The whole spreadsheet in this example is supposed to be 3,252 rows, and I've got others that are tens of thousands of rows deep, so copying and pasting individual cells would be disastrously ineffective.

 

[sracer]

Ok sorry for taking so long to reply, but here is what I got when I tried to copy and paste a single cell into Numbers. The text was this: 0800101108

And it came out like this, when copying the text into cell 17A, formatted as text, as shown:
View attachment 811981

So no problems here. The 0 at the start wasn't removed. So I tried to copy and paste a full row into Numbers, and the text was this: 0800101108,7,Adams 108,Adams,1354,503,10,805,11,6,19,250,148,90

I copied it into the same cell, with the same format, and this came out:
View attachment 811982

This is undesirable, as I wanted everything between the commas in separate boxes. So I tried to copy and paste multiple rows into the cell. The rows that I used were these:

0800101108,7,Adams 108,Adams,1354,503,10,805,11,6,19,250,148,90
0800101109,7,Adams 109,Adams,2936,930,72,1874,9,24,27,515,300,193
0800101098,7,Adams 98,Adams,3135,1791,60,1123,61,7,93,1117,666,431

And they came out:
View attachment 811985
This is what I wanted before, because the boxes were separated, but as you can see, the boxes in column A are formatted differently, even though I set them as text. The 0800101108, for instance, came out as 800101108. Apparently this only happens when multiple rows of text are copied and pasted into the spreadsheet.

The whole spreadsheet in this example is supposed to be 3,252 rows, and I've got others that are tens of thousands of rows deep, so copying and pasting individual cells would be disastrously ineffective.

Ah, now I see where the confusion is. There are actually two separate issues that need to be dealt with. The first is getting comma separated values to appear in separate cells. The second is getting the numbers to format as text.

Here are the steps that should accomplish what you need...

1. Open TextEdit and create a new temporary document.
2. Insert a tab character on the first line and then press [return] to create a new line.
3. Go to where the comma separated values are and copy all of it to the clipboard.
4. Go back to TextEdit and paste that data there (starting at the new line).
5. Go to the first character in the first line, select the tab character that was previously inserted, and copy it to the clipboard.
6. (still in TextEdit) Edit -> Find -> Find and Replace...
7. In the find and replace dialog, enter a comma (,) in the top entry and paste the tab into the lower entry. Click on the (All) button. (this will replace all commas with tabs).
8. Go to Numbers and select the entire table and change the cell formatting to "text".
9. Go back to TextEdit, copy all of the data (except the first line) to the clipboard.
10. Go back to Numbers, click on the upper left corner cell where the data is to be pasted and paste the data.

That should do it.

 

[nj-morris]

Ah, now I see where the confusion is. There are actually two separate issues that need to be dealt with. The first is getting comma separated values to appear in separate cells. The second is getting the numbers to format as text.

Here are the steps that should accomplish what you need...

1. Open TextEdit and create a new temporary document.
2. Insert a tab character on the first line and then press [return] to create a new line.
3. Go to where the comma separated values are and copy all of it to the clipboard.
4. Go back to TextEdit and paste that data there (starting at the new line).
5. Go to the first character in the first line, select the tab character that was previously inserted, and copy it to the clipboard.
6. (still in TextEdit) Edit -> Find -> Find and Replace...
7. In the find and replace dialog, enter a comma (,) in the top entry and paste the tab into the lower entry. Click on the (All) button. (this will replace all commas with tabs).
8. Go to Numbers and select the entire table and change the cell formatting to "text".
9. Go back to TextEdit, copy all of the data (except the first line) to the clipboard.
10. Go back to Numbers, click on the upper left corner cell where the data is to be pasted and paste the data.

That should do it.

No, still not working. Copying one line and pasting it onto the spreadsheet works fine, but any more than that just gives me the same result.

 

[sracer]

No, still not working. Copying one line and pasting it onto the spreadsheet works fine, but any more than that just gives me the same result.

Hmm. Ok, I just confirmed that this works correctly with Numbers (from iWork 09) but is broken in the latest version of Numbers. Sadly, this is part of the dumbing down of iWork that occurred.

Here's a screenshot of it working with the older version of Numbers.

 

[nj-morris]

Hmm. Ok, I just confirmed that this works correctly with Numbers (from iWork 09) but is broken in the latest version of Numbers. Sadly, this is part of the dumbing down of iWork that occurred.

Here's a screenshot of it working with the older version of Numbers.
View attachment 811989

Eh, I'll just use find and replace. Thanks anyway though.

 

[Conutz]

Traditional comma-separated should be displayed correctly if opened from a .csv file in Numbers. This depends on your number format set up on your Mac of course. If memory serves, having the separator set up as a comma (and decimal as a point) should do it. I.e.:

1. Check that Mac OS number formatting is set for a comma as the list separator.
2. Save your comma-delimited content to a text file with extension .csv.
3. Open the file in Numbers and it should display correctly.

This is from memory, but I open .csv files in Numbers frequently without a problem.

 

[sracer]

Traditional comma-separated should be displayed correctly if opened from a .csv file in Numbers. This depends on your number format set up on your Mac of course. If memory serves, having the separator set up as a comma (and decimal as a point) should do it. I.e.:

1. Check that Mac OS number formatting is set for a comma as the list separator.
2. Save your comma-delimited content to a text file with extension .csv.
3. Open the file in Numbers and it should display correctly.

This is from memory, but I open .csv files in Numbers frequently without a problem.

Correct. But the problem has to do with Numbers treating zero-leading numeric text as numbers and stripping off the leading zero.

 

[BrianBaughn]

What formatting exists on the original file with the data? Have you tried converting that file to plain text, if it isn't already, before copying/pasting?

 

[Conutz]

Correct. But the problem has to do with Numbers treating zero-leading numeric text as numbers and stripping off the leading zero.

Oh okay, thanks. Will try it out on the Mac tomorrow.

 

[harriska2]

Does putting quotes around the numbers with leading zeros then importing the file help? Do you rename the file so it has a .csv extension?

 

[neutrino23]

Maybe this will help. Look at creating a custom format. The description specifically talks about leading zeros:

You can create your own cell formats for displaying numbers, text, and date and time values. Your custom formats are saved automatically and listed in the Data Format pop-up menu so you can use them again.

You might need to create a custom text format for a numeral so that you can use it as a text label. For example, if you enter a zip code with a leading zero, Numbers would normally treat it as a number and remove the leading zero automatically. If you format the zip code as text, that doesn't happen.

Note: To create a custom format for numerals to be used as text labels, see “Create a custom text format,” below.
​

I've tried working with this and can't get it to do what you want. I confirmed that pasting a single row works OK but multiple rows are treated differently. Pasting multiple rows clips leading zeros. I would report this to Apple. Maybe they'll fix it.

Also found that if I paste in a single row of text delimited by commas then it pastes it into one cell. If I replace the commas with tabs then it properly puts one item in each cell and does not clip the leading zeros. If I copy multiple lines delimited by tabs it properly separates each item but now it clips leading zeros.