# Compiling a list of separate strings in a range in Excel **URL:** \ **Category:** Factual Questions\ **Created:** [December 10, 2009, 4:45am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696 "2009-12-10T04:45:33Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![matt\_mcl](https://avatars.discourse-cdn.com/v4/letter/m/7ab992/32.png) [@matt\_mcl](https://boards.straightdope.com/u/matt_mcl)\ **Post date:** [December 10, 2009, 4:45am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/1 "2009-12-10T04:45:33Z")
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: ```auto Montreal, metro, bus, train Montreal, train Montreal, bus map, train map, metro map London, bus map ``` I’d like to get either: ```auto bus bus map London metro metro map Montreal train train map ``` or at least ```auto bus, bus map, London, metro, metro map, Montreal, train, train map ``` Is this possible? ---
**Author:** ![Nava](https://avatars.discourse-cdn.com/v4/letter/n/da6949/32.png) [@Nava](https://boards.straightdope.com/u/Nava)\ **Post date:** [December 10, 2009, 4:55am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/2 "2009-12-10T04:55:29Z")
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. ---
**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\ **Post date:** [December 10, 2009, 5:27am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/3 "2009-12-10T05:27:53Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [December 10, 2009, 9:58am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/4 "2009-12-10T09:58:06Z")
> [@xash](#): > > 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. 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. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [December 10, 2009, 2:11pm UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/5 "2009-12-10T14:11:43Z")
> [@Magiver](#): > > 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. Then after that you can used Advanced Filter to winnow the list down to unique terms. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [December 10, 2009, 9:08pm UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/6 "2009-12-10T21:08:23Z")
> [@CookingWithGas](#): > > Then after that you can used Advanced Filter to winnow the list down to unique terms. 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. ---
**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\ **Post date:** [December 10, 2009, 9:12pm UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/7 "2009-12-10T21:12:21Z")
> [@Magiver](#): > > 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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [December 11, 2009, 8:14am UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/8 "2009-12-11T08:14:51Z")
> [@Duke](#): > > 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. Tried it and it copied only the filtered items. So that would be faster than a Pivot table. ---
**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\ **Post date:** [December 11, 2009, 10:32pm UTC](https://boards.straightdope.com/t/compiling-a-list-of-separate-strings-in-a-range-in-excel/520696/9 "2009-12-11T22:32:32Z")
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.