Solved I want separate email ids in excel sheet

June 23, 2013 at 10:37:35
Specs: Windows 7
I want separate email ids in excel sheet like this
I have email ids like this "myidentifiers@example.com.au" and I want to remove last two words "au" and want like this myidentifiers@example.com

second one I want to separate email ids by name ( Not domains example * gmail.com) for example
me@gmail.com
128@gmail.com
128@yahoo.com
1258@gmail.com
1258@yahoo.com
me@yahoo.com

I want only " ME" email ids only
me@gmail.com
me@yahoo.com


See More: I want separate email ids in excel sheet

Report •


#1
June 23, 2013 at 13:05:34
✔ Best Answer
For your first requirement, there are a numerous solutions, depending on what the rest of your data looks like.

Based strictly on your example, where there are 3 characters that you want to eliminate (.au) this will work. It simply returns three less characters than the original string, starting from the left:

=LEFT(A1,LEN(A1)-3)

If the part you want to strip off might contain something other than 3 characters, then we need to find the 2nd dot in the string and remove it and everything after it. This formula should work regardless of how many characters follow the 2nd dot.

=LEFT(A1,FIND(".",A1,FIND(".",A1)+1)-1)

For your second question, your text doesn't match your screen capture, so I don't really know what you are looking for.

Your text says "I want only "ME" email ids only" but your screen capture shows support@168.com in the same column as the "me" emails.

What exactly are you looking for?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 24, 2013 at 02:15:26
thanks for your solution

my second problem : Sorry for that Support is missing

here I want to do

"I want only "ME" & "support" email ids only"


Report •

#3
June 24, 2013 at 05:59:36
This should work for you.

You will note that I included the @ in the search string so that the formula will not return items such as meany@something.com or support_tech@usus.org

This will return the "me" and "support" addresses:

=IF(OR(LEFT(A1,3)="me@",LEFT(A1,8)="support@"),A1,"")

This will return all the others:

=IF(NOT(OR(LEFT(A1,3)="me@",LEFT(A1,8)="support@")),A1,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 24, 2013 at 07:25:34
Thank you very much got the solution

Report •


Ask Question