Compiling a list of separate strings in a range in Excel

General Questions
#1

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

Is this possible?

#2

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.

#3

It is possible. I just tried it, and it seemed to work.

  1. Save file as a .txt file
  2. Open it in Word
  3. Use Find/Replace to remove all " in the document, leaving only commas
  4. Save as .txt file
  5. Open the .txt file in Excel
  6. It will ask for delimiter, specify comma as the delimiter
  7. Open file in Excel
  8. All words should now display on the same row
  9. Use Transpose to make all display in one column instead of row
  10. Use the delete duplicates option to delete duplicates
  11. I ended up with:

Montreal
metro
bus
train
bus map
train map
metro map
London

If that’s all too confusing for you, email me the file and I’ll fix it for you.

#4

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.

#5

Then after that you can used Advanced Filter to winnow the list down to unique terms.

#6

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.

#7

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.

#8

Tried it and it copied only the filtered items. So that would be faster than a Pivot table.

#9

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.