Solved I want to separate email ids by name

September 25, 2013 at 15:49:09
Specs: Windows 7
I want to separate email ids by name
me@gmail.com
Support@123.com
128@gmail.com
128@yahoo.com
1258@gmail.com
1258@yahoo.com
me@yahoo.com

I want separate “ME & Support” email ids only
me@gmail.com
me@yahoo.com
Support@123.com
Presently using this formula
=IF(NOT(OR(LEFT(A1,3)="me@",LEFT(A1,8)="support@")),A1,"")
By using this formula eliminating “me & support”
This formula is use full up to some extension.
I want to use it for 1500 words
In the above example we have only two words (me and support)
Examples
applicant@
applyc@
application@
apply1@
apply-london@
applicant@
applyk@
applym@
applicationssupport@
applyi@
ETC


See More: I want to separate email ids by name

Report •


#1
September 25, 2013 at 18:06:36
It's not perfectly clear to me what you are trying to do, but I'll offer a suggestion anyway. Let us know if I misunderstood your requirements.

Let's say you start with this:

            A
1   applicant@yahooo.com
2   applyc@gmailo.com
3   application@gusa.net
4   apply1@comcom.com

Insert a new Column A, enter this formula in A1 and drag it down:

=LEFT(B1,FIND("@",B1))

You should end up with this:

        A                 B
1   applicant@    applicant@yahooo.com
2   applyc@       applyc@gmailo.com
3   application@  application@gusa.net
4   apply1@       apply1@comcom.com

Now with your list of required "names" starting in C1, put this formula in D1 and drag it down to the bottom of your list:

=VLOOKUP(C1,$A$1:$B$4,2,0)

You should end up with this, with only those email addresses that match the list in Column C:

         A               B                 C                D
1 applicant@    applicant@yahooo.com   applicant     applicant@yahooo.com   
2 applyc@       applyc@gmailo.com      application   application@gusa.net
3 application@  application@gusa.net
4 apply1@       apply1@comcom.com

Note: The reason we inserted the new Column A is because VLOOKUP only looks at the left-most column of a range and returns values from columns on the right.

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


Report •

#2
September 26, 2013 at 05:56:50
=VLOOKUP(C1,$A$1:$B$4,2,0) results #N/A

exact problem is I want to eliminate emails id by the name like " me, Texas, support"

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

This formula is useful up to some extent only like up to fifty names.

I need it for 1500 names , like applicant , HR , apply, info, public.. etc


Report •

#3
September 26, 2013 at 07:42:37
✔ Best Answer
My error. If Column A contains the @ symbol after each entry, then Column C must also have the @ symbol since VLOOKUP needs an exact match.

      A                 B                  C                  D
1 applicant@    applicant@yahooo.com   applicant@     applicant@yahooo.com


On the other hand, if you use this formula, then the @ will not appear in Column A and you won't need the @ in Column C. It all depends on what your list looks like.

=LEFT(B1,FIND("@",B1)-1)

      A                 B                  C                  D
1 applicant    applicant@yahooo.com   applicant     applicant@yahooo.com


Explanation:

FIND(find_text, within_text)

The FIND function returns the numerical position of the character you are searching for (find_string = "@") within the text sting you are searching (within_text = B1).

LEFT(text, [num_chars])

The LEFT function then uses the value returned by FIND as the num_char argument to return a specific number of characters from the original text (text = B1)

If we want the @, we just use the value returned by FIND. If we don't want the @, then we subtract 1 from the value returned by FIND to return one less character.

One thing that is not clear to me. Is the list you will use in Column C a list of the items you want to keep or a list of the items you want to eliminate? If you have a list of items you want to keep, the suggested method should work for you. If not, then a few extra steps might be required.

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


Report •

Related Solutions


Ask Question