Skip navigation

splitting data up into two columns

319 Views 7 Replies Latest reply: Jan 23, 2013 12:09 AM by Barry RSS
alanbutter Calculating status...
Currently Being Moderated
Jan 22, 2013 3:44 AM

Apologies if this is covered previously ( i'm sure it probably is) i've imported .csv into numbers but the first cell contains two pieces of information separated by a space (TI NUMEROLOGY etc) how do I separate into two cells so I can sort data by the different identifers (TI = Title)

 

 

**

IB 1859060196

BI PAPERBACK

AU SHINE

BC VXFN

CO UK

PD 19990923

NP 128

RP 9.99

RI 9.99

RE 9.99

PU CONNECTIONS BOOK PUBLISHING

YP 1999

TI NUMEROLOGY

TI YOUR CHARACTER AND FUTURE REVEALED IN NUMBERS

EA 9781859060193

RF R

SG 2

GC M01

DE A unique step-by-step visual approach to numerology

DE characters and compatibility from names and birth dates.

Mac OS X (10.6.8)
  • Yellowbox Level 4 Level 4 (3,990 points)
    Currently Being Moderated
    Jan 22, 2013 4:03 AM (in response to alanbutter)

    Hi Alan,

     

    In Cell C2, the formula is =LEFT(B2,2)

     

    (and Fill Down)

     

    Screen Shot 2013-01-22 at 10.55.00 PM.png

     

    Ian.

  • Badunit Level 6 Level 6 (10,775 points)
    Currently Being Moderated
    Jan 22, 2013 1:03 PM (in response to alanbutter)

    Close but not quite. If you look at the description for the RIGHT function, you'll see that the formula you proposed will give you only the rightmost two characters of the original string.  You want all characters except the leftmost three characters.

     

    =RIGHT(B2,LEN(B2)-3)

  • Yellowbox Level 4 Level 4 (3,990 points)
    Currently Being Moderated
    Jan 22, 2013 5:39 PM (in response to alanbutter)

    Hi Alan,

    Another thought. You can sort the data as it is. Click on Reorganize button on the ToolBar:

     

    Screen Shot 2013-01-23 at 12.22.08 PM.png

     

    To get:

     

    Screen Shot 2013-01-23 at 12.26.31 PM.png

    Every cell starting with TI and a space will come together, and sorted by whatever follows TI and a space.

     

    However, if it looks nicer to split the entries, use Badunit's formula.

     

    Alan wrote: "I also clicked on C2 and went to insert/fill/ but the options are greyed out o I can'r apply to all unless I do it manually... and it's a mahoosive file."

     

      To fill down, click on C2 then drag the white handle down. Or, select the rows that you want to fill before Insert > Fill (Numbers needs to know how far to fill).

     

    Ian.

    MacBook Pro (13-inch, Mid 2012), OS X Mountain Lion (10.8.2)
  • Yellowbox Level 4 Level 4 (3,990 points)
    Currently Being Moderated
    Jan 22, 2013 6:03 PM (in response to Yellowbox)

    P.S. After you split the cells, be sure to sort Entire Table!

     

    Ian.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jan 23, 2013 12:09 AM (in response to alanbutter)

    Hi Alan,

     

    This data looks similar to the MARC record which libraries use to describe a book in their collection. All of the data in your sample applies to a single item (book), and all of it should be kept together, or set out in a way that allows it t0 be re-collected into a group after the sorting you mention above, The easiest way to accomplish this is probably to use a column where you number the rows of the table BEFORE sorting. A sort on that column would then restore the original order, bringing  each record's data lines together again.

     

    To number the lines, place the formula below into the first non-header cell of any column:

     

    = ROW()-1

    (where 1 represents the number of Header rows above the first data row)

     

    Then:

    Click the Column Reference tab to select the whole column.

    Command-click on each of the selected cells that is in a Header row to deselect that cell.

    When the cell containing the formula is the first selected cell in the column, go Insert > Fill > Fill Down to fill the formula into all rows below the first.

    With the cells still selected, go Edit > Copy to copy the cells, then

    Go Edit > Paste Values to replace the formulas with the calculated results.

     

    To return the table to it's original order, Sort the whole table on this column.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.