• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to clean up a list of names. [SOLVED]

J

James Perry

Member
Hello friends of chandoo.org & here's my Q.

How do I clean up a list of names?

Sometimes there is only one name, which I know how to fix.

But sometimes there are multiple names. This is the recipient field from a long list of emails.

Entries are in this form:

Angie Klare <AKlare@media.com>

Terra Harbaugh <THarbaugh@media.com>; Chelsea Smith <CSmith@media.com>

Terra Harbaugh <THarbaugh@media.com>


. . . should become

Angie Klare

Terra Harbaugh; Chelsea Smith

Terra Harbaugh


Any way to handle these with a single function, or just a couple steps (involving T2C, etc.)?

Thanks in advance.

James
 
Hi James Perry,


Is this data present in a single coulumn and are there more then two email ids in one cell?


Regards,
 
Hi James ,


You can use T2C provided you replace one of the delimiters "<" or ">" by the other , so that your list has only one delimiter , either "<" or ">".


After this step , use T2C , and skip all the columns delimited by the "<" or ">" characters.


As a last step , concatenate the text from all the columns.


Narayan
 
Hi James,


Here's a solution for the example given...so I assume no more than two email address's. It's rather a long formula but it does work, I'm sure someone could come up with a shorter one maybe?


=IFERROR(REPLACE(REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""),FIND(" <",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""),FIND(";",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""))),FIND(">",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""),FIND(" <",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""),FIND(";",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""))))-FIND(" <",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""),FIND(";",REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,"")))+1,""),REPLACE(A1,FIND(" <",A1),FIND(">",A1)-FIND(" <",A1)+1,""))
 
Good day oldchippy


The contest for the longest FIND/REPLACE formula was closed some time ago......but you would have won it by a mile :)
 
Hi James ,


One more alternative ; check the file here :


https://www.dropbox.com/s/5m2lytqjrgrdtgo/James_example.xlsx


Narayan
 
@oldchippy, b(ut)ob(ut)hc

Hi!


I knew it could be done... here's a longer formula:


=TRIM(LEFT(REPLACE(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""),IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1),IFERROR(SEARCH(";",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""),IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1)+1),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1)-IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1),""),LEN(REPLACE(R

EPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""),IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1),IFERROR(SEARCH(";",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""),IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1)+1),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1)-IFERROR(SEARCH("<",REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),"")),LEN(REPLACE(A1&";",SEARCH("<",A1&";"),SEARCH(";",A1&";",SEARCH("<",A1&";")+1)-SEARCH("<",A1&";"),""))+1),""))-1))


2112 vs. 619 of oldchippy :p


Regards!
 
Good evening SirJB7


It is obvious from the look of your code you have had one or two Carlsbergs....but I am sure it will all merge together through the bottom of a malt glass
 
@b(ut)ob(ut)hc

Hi, my friend!

Good afternoon.

Did you say one? That word shouldn't be used as quantitative adjective in the same sentence where Carslberg is...

Regards!
 
Hello James,

One more approach:

Assuming your source string is in A1, Put the following formula in cell B1, and copy to the right to C1, D1, etc. till you get a clean string

=IFERROR(TRIM(REPLACE(A1, FIND("<", A1), FIND(">", A1)-FIND("<", A1)+1, "")), TRIM(A1))


This approach should work even if you have more than two e-mail addresses.


Cheers,

Sajan.


P.S.

SirJB7: That is clearly the longest string of any kind (formula or otherwise) that I have ever seen!!
 
this formula will handle any length, and will split each email address out into a separate column. Assuming your string is in A2, enter this into B2 and drag across.:

[pre]
Code: 
=IFERROR(MID($A2,FIND("|",SUBSTITUTE("; "&$A2,"; ","|",COLUMNS($B:B))),FIND("|",SUBSTITUTE($A2," <","|",COLUMNS($B:B)))-FIND("|",SUBSTITUTE("; "&$A2,"; ","|",COLUMNS($B:B)))),"")
[/pre]
 
And this User Defined Function is what i would actually use. It will do exactly what you asked, i.e. strip out the email addresses, and leave everything else in one column.


Put this in a code module in the workbook, and call it from the workbook like so:

=CleanEmail(A2)

[pre]
Code: 
Public Function CleanEmail(sTarget As String)
Dim lCount As Long
Dim sReplace As String
For lCount = 1 To UBound(Split(sTarget, "<"))
sTarget = Replace(sTarget, Mid(sTarget, InStr(sTarget, "<"), 1 + InStr(sTarget, ">") - InStr(sTarget, "<")), "")
Next lCount

CleanEmail = sTarget
End Function
[/pre]
 
here's a generic User Defined Function, that can be re-used using other delimiters.

You call it like this:

=StripText(String, Opening_Delimiter, Closing_Delimiter)


...which for the above example would be like this:

=StripText(A2,"<",">")

[pre]
Code: 
Public Function StripText(sTarget As String, sOpening_Delimiter As String, sClosing_Delimiter As String) As String
Dim lCount As Long

For lCount = 1 To UBound(Split(sTarget, sOpening_Delimiter))
sTarget = Replace(sTarget, Mid(sTarget, InStr(sTarget, sOpening_Delimiter), 1 + InStr(sTarget, sClosing_Delimiter) - InStr(sTarget, sOpening_Delimiter)), "")
Next lCount

StripText = sTarget
End Function
[/pre]
 
@Sajan

Hi!

I haven't checked it before but it beats oldchippy's previous record:

http://chandoo.org/forums/topic/distributing-demand-as-per-selling-price#post-26580

Regards!

PS: I need two more (not one!)... Carlsbergs...
 
Hi Pablo,


2112 x $0.10/Character = $211 / $2 per Can of Carlsberg = 105 Cans approximately :D


Regards,
 
Thank you all!!! :)


I think it would be easier to just replace the email addresses with a blank.

Here's how i did it. ( Keeping it simple.)


1. Press Ctrl+H.

2. Notice that the email addresses has this pattern, <email@address.com>.

3. In the "Find What" field, enter <*>

4. Leave the "Replace with" field as it is, i.e. blank

5. Click on "Replace All"

6. Make sure that the "Match entire cell contents" is unchecked.


Thank u guys & stay blessed.

James
 
Hi James,


Just one thing to bear in mind is that when you do your FIND/REPLACE include a space before <*> or else it with leave a space at the end of the name - it may not matter but I just thought I'd mention it.
 
You must log in or register to reply here.
Back
Top