Within single cell, return text value from between 2 consistent known border values

[insdotwest]

Hello, everyone. My first post/question. Thank you in advance for the help.

I need to determine unique company names from within a list of clients, 12,000+. There are numerous variations on how the company names are presented. I don't feel like trying in Excel to go after disambiguating this.

Fortunately I have email addresses associated, so something with some consistency. But I still need to get to a count of unique names. So, I am orienting upon the domain name, between the "@" and ".com".

How do I return this text value which is in a single cell? I need to do this so I can then know how many unique companies I have within this list, for which I will then simply run a FREQUENCY formula?

Thoughts? Much obliged.

 

[eddvrs]

This should get you started:

=MID(A1,FIND("@",A1)+1,FIND(".c",A1,FIND("@",A1))-FIND("@",A1)-1)

Assuming mail address is in A1, this will return everything inbetween "@" and ".c" in the email address. You could modify this if you have different TLDs (such as .org or .ch)

 

[oldbrewer]

abccompany@zzz.com

how does zzz help you determine if abcccompany is the same as abccompany if they are both .com

 

[AlanY]

Hello, everyone. My first post/question. Thank you in advance for the help.

I need to determine unique company names from within a list of clients, 12,000+. There are numerous variations on how the company names are presented. I don't feel like trying in Excel to go after disambiguating this.

Fortunately I have email addresses associated, so something with some consistency. But I still need to get to a count of unique names. So, I am orienting upon the domain name, between the "@" and ".com".

How do I return this text value which is in a single cell? I need to do this so I can then know how many unique companies I have within this list, for which I will then simply run a FREQUENCY formula?

Thoughts? Much obliged.

you can use =find("@",A1) to locate the @, and
=find(".com",A1) for the .com

then use =MID() to extract the middle bit,
=MID(A1,FIND("@",A1)+1,FIND(".com",B4)-FIND("@",B4)-1)

 

[insdotwest]

This should get you started:

=MID(A1,FIND("@",A1)+1,FIND(".c",A1,FIND("@",A1))-FIND("@",A1)-1)

Assuming mail address is in A1, this will return everything inbetween "@" and ".c" in the email address. You could modify this if you have different TLDs (such as .org or .ch)

Thank you, eddvrs! Perfect. I appreciate the solutio and education.
insdotwest

 

[insdotwest]

Thank you, AlanY! As with eddvrs, you were on the money, and I am thankful for your help in solution and education.
insdotwest

 

[insdotwest]

abccompany@zzz.com

how does zzz help you determine if abcccompany is the same as abccompany if they are both .com

@ oldbrewer: I was looking for the simplest way of getting at how many individuals I had and how many unique companies were in this list. Of all my data, because of poor data entry to begin with, I could not rely on 'company name', so the domain name looked to be the area within which I'd find the greatest consistency. It also had the added effect of breaking across subsidiary names because these too resolved to a consistent domain name.

 

[AlanY]

Thank you, AlanY! As with eddvrs, you were on the money, and I am thankful for your help in solution and education.
insdotwest

you're welcome