Answer Best answer as chosen by user Nielleshee
Well I can do this
but it's not pretty. I never do pretty in spreadsheets
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).
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?
Was this reply helpful? (1) (0)