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?

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"trinatoa" wrote:

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?

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

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?

--

Dave Peterson

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

"JBeaucaire" wrote:

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"trinatoa" wrote:

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?