Extracting data from cell based on a character

Question

Hi,

Using excel 2007.

I have a sheet with a list of all company contacts, specifically e-mail addresses. I only want to extract the part of the address from the @ sign (i.e the domain name).

Example *** Email address is removed for privacy ***, *** Email address is removed for privacy ***

result in adjacent cell should be @pink.com for the *** Email address is removed for privacy *** and @purple.com for the *** Email address is removed for privacy ***. needles to say, this list is a few thousand rows, and doing this manually will be a real pain.

Sean

Answer 1

nevermind, figured it out.

Just in case someone wants to do something similar, here is a possible solution

Column A has the addresses

in Column B I ran this formula =FIND("@",A2) - This gives the number of characters before the @ sign. Could not find a function that gave me the number of characters after the @ sign

In Column C I used this =LEN(A2) to find the total length of the text

In column D I used =RIGHT(A2,C2-B2+1) this basically grabs the characters from the right where result is Column C total Less Column B total plus 1 (This is because i wanted the @ sign included in the result.

There is probably a simpler way to do this... suggestions welcome

Answer 2

Just to be cute:

=REPLACE(A1,1,FIND("@",A1),"@")

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Try this...

=MID(A2,FIND("@",A2),100)

--

Biff

Microsoft Excel MVP

Answer 4

Sean,

Well done for working out a solution but you may want to consider doing it in a single cell

=MID(A1,FIND("^^",SUBSTITUTE(A1,"@","^^",1))+1,LEN(A1))

---

If this post answers your question, please mark it as the Answer.

Answer 5

Thank you Sean for giving out the resolution, I am sure it will help out most of the community users.