This is a bit complex and it may well not be a task to which Excel is best suited, but here’s what it is: In a worksheet I have a column containing text strings consisting of several phrases separated by commas. What I’d like to do is to extract all the unique expressions, either into one big string or into several.
So basically, with input:
Montreal, metro, bus, train
Montreal, train
Montreal, bus map, train map, metro map
London, bus map
I’d like to get either:
bus
bus map
London
metro
metro map
Montreal
train
train map
or at least
bus, bus map, London, metro, metro map, Montreal, train, train map
I don’t have excel so I can’t give you the exact formulas, but a process that would work (probably not optimal) is:
with the biggest length of your strings in number of words being N, create in the N columns to the right of the column with your input a series of formulas that select “everything up to the first coma”, “everything between the first and second coma”… etc
then just copypaste the values of those columns into a single column
and use Special Filter to copy Single Values from that column into another one - tadaaa!
The hard part is the first one, of course, but I’ve done it. A simpler although less compact way to do it is use two columns to the right of your input:
column input+1 gets everything to the left of the first coma
column input+2 gets everything to the right of the first coma
leave i+3 blank; copypaste the formulas from i+1 and i+2 into i+4 and i+5, copypaste the values from i+2 into the column you left blank. Repeat as many times as needed before copypasting i+1, i+4 (the ones where you’ve been reading each succesive value)… into a single column and filtering it.
You could do the same thing with the “text to column” feature of excel. highlight the column, From the “Data” function choose “text to column” , click on delimiter, then next, then click on “comma”. You’re finished.
Or make a pivot table so you have something that can be copied and pasted. For either to work the multiple columns of information would have to be copied and pasted into one column.
Or you could use VBA and do a For-Next loop and…no, just kidding.
Actually, with Advanced Filter when you blip out duplicated values, I believe you can copy and paste a column as with a pivot table. I could be wrong…unfortunately I can’t check that at the moment.
I suggest using Text->Columns and selecting the , as the delimiter, as has been suggested. Then put it all together and pull out the unique ones. See the other thread on how to find first occurences for how to do this.