partial/absolute text match

G

george

I have two text columns A and B. Column A contains names and B contains
e-mail addresses. The first part of the e-mail address in B (before the
@) is sometimes the same or somewhat similar to the name in A, and
sometimes completely different:

A B
1 steve [email protected]
2 colin [email protected]
3 john [email protected]

I want to keep only those rows in which the name and the first part of
the e-mail (before the @) partially or completely match (in the above
example, I want to keep rows 1 and 2 and delete row 3).

Can I do it in Excell XP?
 
D

Dave Peterson

You could use a formula like this to see if column B starts with the same
characters that are in column A:

=IF(A1=LEFT(B1,LEN(A1)),"Match","Not A Match")

If you want "Colin" to match up with "[email protected]", you could use a
formula like:

=IF(ISNUMBER(SEARCH(A1,LEFT(B1,FIND("@",B1)-1))),"Match","Not A Match")
 
K

Kishor

Hi,

We can solve this problem by creating custom function to match strings.

Regards,
Kishor.
 
K

Kishor

Hi,

We can solve this problem by creating custom function to match strings.

Regards,
Kishor.
 
Top