Problems with Numbers, please help.

Discussion in 'Mac Apps and Mac App Store' started by nj-morris, Dec 17, 2018.

  1. nj-morris macrumors 68000

    nj-morris

    Joined:
    Nov 30, 2014
    Location:
    UK
    #1
    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?
     
  2. adrianlondon macrumors 6502a

    adrianlondon

    Joined:
    Nov 28, 2013
    Location:
    Switzerland
    #2
    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.
     
  3. sracer macrumors 604

    sracer

    Joined:
    Apr 9, 2010
    #3
    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. :D
     
  4. nj-morris thread starter macrumors 68000

    nj-morris

    Joined:
    Nov 30, 2014
    Location:
    UK
    #4
    Yeah I've tried that:

     
  5. adrianlondon macrumors 6502a

    adrianlondon

    Joined:
    Nov 28, 2013
    Location:
    Switzerland
    #5
    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.
     
  6. sracer macrumors 604

    sracer

    Joined:
    Apr 9, 2010
    #6
    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?
     
  7. nj-morris, Dec 21, 2018
    Last edited: Dec 21, 2018

    nj-morris thread starter macrumors 68000

    nj-morris

    Joined:
    Nov 30, 2014
    Location:
    UK
    #7
    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:
    Screenshot 2018-12-21 at 10.59.49.png

    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:
    Screenshot 2018-12-21 at 11.06.51.png

    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:
    Screenshot 2018-12-21 at 11.10.46.png
    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.
     
  8. sracer macrumors 604

    sracer

    Joined:
    Apr 9, 2010
    #8
    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.
     
  9. nj-morris thread starter macrumors 68000

    nj-morris

    Joined:
    Nov 30, 2014
    Location:
    UK
    #9
    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.
     
  10. sracer macrumors 604

    sracer

    Joined:
    Apr 9, 2010
    #10
    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.
    Screen Shot 2018-12-21 at 5.54.13 AM.png
     
  11. nj-morris thread starter macrumors 68000

    nj-morris

    Joined:
    Nov 30, 2014
    Location:
    UK
    #11
    Eh, I'll just use find and replace. Thanks anyway though.
     
  12. Conutz macrumors regular

    Conutz

    Joined:
    Oct 24, 2014
    Location:
    Joburg
    #12
    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.
     
  13. sracer macrumors 604

    sracer

    Joined:
    Apr 9, 2010
    #13
    Correct. But the problem has to do with Numbers treating zero-leading numeric text as numbers and stripping off the leading zero.
     
  14. BrianBaughn macrumors 603

    BrianBaughn

    Joined:
    Feb 13, 2011
    Location:
    Baltimore, Maryland
    #14
    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?
     
  15. Conutz macrumors regular

    Conutz

    Joined:
    Oct 24, 2014
    Location:
    Joburg
    #15
    Oh okay, thanks. Will try it out on the Mac tomorrow.
     
  16. harriska2 macrumors 6502a

    harriska2

    Joined:
    Mar 16, 2011
    #16
    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?
     
  17. neutrino23, Jan 3, 2019
    Last edited: Jan 3, 2019

    neutrino23 macrumors 68000

    Joined:
    Feb 14, 2003
    Location:
    SF Bay area
    #17
    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.
     

Share This Page