Well I can do this

by MarkFlax Moderator - 6/20/12 12:43 PM

In Reply to: Parsing data in iWork Numbers? by Nielleshee

but it's not pretty. I never do pretty in spreadsheets happy

And it is not Mac's iWorks Numbers, but Windows' LibreOffice spreadsheet; however the method will be the same.

I am sure there are much easier ways to do it, either using visual basic, (or the Mac macro equivalent), or specialised functions, but I've used just TEXT functions.

Working on just apple/orange in C4 (I always leave gaps top and sides when I start as I very often have to add data and columns).

C4=apple/orange

H4 finds the position of the slash, "/" with this; =SEARCH("/",C4,1) where 1 is the start of the text string. Answer = 6

I4 finds the length of the whole string with this; =LEN(C4) Answer = 12

J4 returns, (shows), the text from the last character to just before the "/"; =RIGHT(C4,I4-H4) Answer = orange

K4 returns, (shows), the first part of the text before the "/" with; =LEFT(C4,H4-1) Answer = apple

D4 now lists the separated 1st part with; =K4 Answer = apple
E4 lists the separated last part with; =J4 Answer = orange

Pull down formulas in all columns (except C), with the fill handle bottom right of the cells.

Columns D and E become the new separated list columns for the data in C

So the result looks something like;

Col D ---|-- Col E

apple ---|- orange
banana -|- pear
grape ---|- fig

Does that help?

Mark