partial/absolute text match

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?

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")



george wrote:
>
> 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?

--

Dave Peterson

Hi,

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

Regards,
Kishor.


"Dave Peterson" wrote:

> 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")
>
>
>
> george wrote:
> >
> > 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?
>
> --
>
> Dave Peterson
>

Hi,

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

Regards,
Kishor.


"george" wrote:

> 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?
>
>