search function using wildcard

[trinatoa]

using excel 2003. i wish to identify the when text from one cell is repeated
in another cell
A2 Mrs Brown, 1 sample road, blurb city.
B2 1 sample road
C2 (contains my function to return a value)

i've tried to used search with a wildcard but it's not working
=search(A2*,B2) help says you can use wildcards but it doesn't give an
example so i guessed. can anyone help?

 

[JBeaucaire]

Close, but backwards, and you have to decide what to do when there is NO
match which will result in an error, not an asnwer of zero. Like this:

=IF(ISNUMBER(SEARCH(B2&"*",A2)),"Found","Not Found")

 

[Dave Peterson]

You don't even need the asterisk as a wild card:

=search(b2,a2)

or maybe:
=isnumber(search(b2,a2))

so you can see True/False instead of numbers and errors.

 

[trinatoa]

thanks for this. It is working sometimes but not other times and I don't
know why. I've been give messy data, perhaps i needed to give you better
examples;

Firstly, what i want to do is concatenate 2 cells into a new field
containing name and address, however, sometimes a part of the name (not
always) has been entered into the address field as well - see below;

example 1 (no problem to merge together):
A2: Joe Smith
A3: 1 Smith Street, Sample City.

example 2 (problem one)
A2: Joe Smith and Jane Smythe
A3: and Jane Smythe, 1 Smith Street, Sample City

I want to identify which ones have the name repeated so I can delete before
concatenating. Your help is HUGELY appreciated!

Trina