Sorting values in each cell separated by semicolon

[Luvsql]

I have a spreadsheet where one column are email addreses (130 rows). The email addresses in each cell are separate by a semicolon, but are not sorted aphabetically nor are they in the same order when they are similar (so I can't use the duplicate conditional format to see if the same ones are used since it could be email1@email.com;email2@email.com in one cell then email2@email.com;email1@email.com).

I'd like to sort the string between the deliminated for each cell. I tried the text to columns, then manually resorted, but then each row could have 1 address, then 2, then 9, etc, so couldn't concatenate etc.

Any thoughts?

 

[xlhelp]

You will need a macro for this. Please post in

http://www.tek-tips.com/threadminder.cfm?pid=707

Avoid Hangovers. Stay drunk.

 

[SkipVought]

Hi,

Here's what your VBA could do. Get all these into one table (column) with a derived column that describes the row each came from.

1) text to columns

2) copy>transform>paste into one column adding the source row in the adjacent column.

Turn on your macro recorder and record these two steps, step 2 for one row. Post your question in forum707 and include your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

Is your objective to get one master list of emails (from all rows) that you can sort in alphabetical order?

If so, consider the following manual procedure:
1. Copy the email addresses in Excel
2. Open a new Word document and do a Paste Special...Text
3. Select the addresses in Word, then do a Replace All
Text to find ;
Text to replace ^p
The ^p is the code for "Paragraph Mark", as can be chosen in the the Special dropdown in the Replace dialog
4. The addresses in Word should now be listed in a single column. Copy them
5. Paste them back in Excel
6. Sort and eliminate duplicates in Excel