## splitting data up into two columns

319 Views 7 Replies Latest reply: Jan 23, 2013 12:09 AM by Barry
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)
• 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)

Ian.

• 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)

• 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:

To get:

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)
• 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.

• 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

#### More Like This

• Retrieving data ...

#### 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.