7 Replies Latest reply: Dec 28, 2012 1:30 PM by Jerrold Green1
Copperhorse83 Level 1 Level 1 (0 points)

I need to paste the info below into multiple cells, instead of the copy going into a single cell !

 

 

 

 

1.     Madsavvy     10     5     7     7     10     9     6     10     4     68

2.     Power of the Mind     5     8     9     8     2     6     9     6     5     58

3.     BAR THA GREAT 1     2     1     8     9     9     7     10     8     2     56

3.     To Your Good Health     9     3     6     10     6     8     7     4     3     56

5.     99 Problems...     1     4     10     3     3     10     8     9     1     49

6.     new year new phil     3     7     4     1     8     4     4     5     10     46

7.     Curtains For All     8     10     2     2     1     5     1     7     8     44

8.     Son of a Champion     7     6     5     4     4     1     2     2     9     40

8.     Pace-a-Bull     4     9     3     5     5     3     3     1     7     40

10.     NEXT     6     2     1     6     7     2     5     3     

 

 

 

 

1.     Madsavvy     291     .476     .787     270     4425     2006     1082      330     266     642

2.     Power of the Mind     288     .457     .812     303     4501     1544     1068     383     2 12     615

3.     BAR THA GREAT 1     297     .438     .706     296     4650     1982     1069     401     220      668

3.     To Your Good Health     308     .471     .756     269     4653     1683     1080     333      188     645

5.     99 Problems...     286     .437     .761     346     4101     1606     1123     36 6     230     694

6.     new year new phil     280     .448     .796     231     3842     1742     886     276     19 7     530

7.     Curtains For All     249     .470     .828     225     3867     1381     1026     239     21 9     543

8.     Son of a Champion     247     .463     .789     264     4157     1627     800     244      178     536

8.     Pace-a-Bull     293     .449     .825     227     4208     1649     864      254     134     555

10.     NEXT     267     .461     .745     211     4242     1706     846     277      321  345


iPad 2, iOS 6
  • 1. Re: I copy multiple numbers from a web page but they paste into a single cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Copper,

     

    The usual cause for pasted data going all into a single cell is that it was pasted into the text layer of the cell. To get to the text layer, you click twice on the cell. The confirmation that you are in the text layer is that you will see a flashing text insertion curson in the cell. So, just click once on a cell and Paste. This should give better results.

     

    Jerry

  • 2. Re: I copy multiple numbers from a web page but they paste into a single cell
    Yellowbox Level 5 Level 5 (4,570 points)

    Hi Copperhorse83,

     

    Here is a workaround. It uses the editing power of Pages (or any wordprocessor) to prepare the text for your spreadsheet. You need tabs between the numbers.

     

    Select and copy the table on the webpage. Paste into Pages and turn on Invisibles (Show Invisibles under the View menu). You will see the spaces (and maybe other hidden characters) between the numbers. Now use the Edit menu to Find > Find... and you will get a dialogue box. 

     

    Use Replace All to replace double spaces with a single space. Keep doing this until only single spaces remain. Other unwanted hidden characters you can remove by selecting and copying them in the document then pasting into the Find box. Replace them with nothing (empty in the Replace box).

     

    Then replace spaces with tabs (the "Insert" drop down menu in this dialogue box). Tabs will separate numbers into different columns when you copy and paste the data into Numbers.

     

    I use Pages to do this preparation because Show Invisibles lets me see how I am prograssing.

     

    Last tricky bit: I don't know how to deal with the spaces between words such as "Power of the Mind"

    in your example.

     

    Good luck,

    Ian.

  • 3. Re: I copy multiple numbers from a web page but they paste into a single cell
    Yellowbox Level 5 Level 5 (4,570 points)

    I wrote:

    Last tricky bit: I don't know how to deal with the spaces between words such as "Power of the Mind"

    in your example.

     

    Now I have the answer. Pic below has three columns (sorry column borders are not shown).

     

     

     

     

     

     

     

     

    Fred

    Nurk

    Fred Nurk

     

     

    Joe

    Blow

    Joe Blow

     

     

     

     

     

     

     

    Column C contains the formula =B2&" "&C2 to join columns A and B with a space between (Fred space Nurk).

    A bit like "Concatenate" in Excel.

     

    Soooooo, insert a column in your spreadsheet to include a formula to link together separate words.

     

    Hope this helps, Ian.

  • 4. Re: I copy multiple numbers from a web page but they paste into a single cell
    Yellowbox Level 5 Level 5 (4,570 points)

    Oops,

    Sorry, got the columns wrong. Column D contains the formula.

    Ian.

  • 5. Re: I copy multiple numbers from a web page but they paste into a single cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Ian,

     

    Thanks for the feedback. Sorry for the misspelling of "cursor". I'm sure you figured it out.

     

    Jerry

  • 6. Re: I copy multiple numbers from a web page but they paste into a single cell
    Barry Level 7 Level 7 (29,180 points)

    Yellowbox wrote:

    "Last tricky bit: I don't know how to deal with the spaces between words such as "Power of the Mind"

    in your example."

     

    The most efficient way to deal with those spaces is to set up Find/Replace to use a different flag for the places to insert tabs. The revision below assumes the raw data begins with at least two spaces separating data items belonging in separate columns.

     

    Find/Replace

     

    First pass:  Find: "  " (two spaces), Replace with: "##". Click Replace All.

     

    Second pass: Find: "## " (note the space), Replace with: "##". Replace All.

     

    Third pass: Find: "##", Replace with: "#" Replace All

    Repeat by clicking Replace All until Find/Replace reports 0 replaced (or ## not found).

     

    Final pass: Find: "#", Replace with: (tab), Replace All.

     

    Select All. Copy. Click on table cell where data is to start (ie. top left), Paste.

     

    Regards,

    Barry

     

    Note: Do not include the quotation marks in the Find/Replace entries.

  • 7. Re: I copy multiple numbers from a web page but they paste into a single cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Ian,

     

    I presumed that Copper had a Tab-Delimited file already, an educated guess from looking at his screen shot. Since my solution worked for him, that must have been the case. Your method is a reasonable approach for free-form records.

     

    Jerry