Wildcard MATCH() breaks on long (?) strings

[Hash]

All -

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and happened to
notice that it skipped an obvious correct match. After attacking that
for too long a time, I found that MATCH() won't handle strings longer
than 255 characters. The skipped cell satisfied ISTEXT(), FIND(),
SEARCH() and menu find efforts. The problem was that the skipped string
was 283 characters long, even though the query string started at 81.

1. Is this documented somewhere? It's not in my Excel function book,
and Google groups didn't readily reveal it.

2. What's the best workaround? Is there a UDF someone can point me to?
Right now I have over a thousand MATCHes to re-evalutate that might have
skipped some cases because the string length was too long. Right now I
plan to match on truncated strings in a helper column. Less than
perfect to say the least.

Thanks for any help.

....best, Hash

 

[Dave Peterson]

Debra Dalgleish has a workaround for using Data|Filter|Autofilter (It suffers
from the same malady.)
http://www.contextures.com/xlautofilter02.html#String

Can you extract the ref from the range into a helper column and then use that
helper column?

Or if you have lots of refs, could you extract them all and concatenate them
into that helper column?

All -

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and happened to
notice that it skipped an obvious correct match. After attacking that
for too long a time, I found that MATCH() won't handle strings longer
than 255 characters. The skipped cell satisfied ISTEXT(), FIND(),
SEARCH() and menu find efforts. The problem was that the skipped string
was 283 characters long, even though the query string started at 81.

1. Is this documented somewhere? It's not in my Excel function book,
and Google groups didn't readily reveal it.

2. What's the best workaround? Is there a UDF someone can point me to?
Right now I have over a thousand MATCHes to re-evalutate that might have
skipped some cases because the string length was too long. Right now I
plan to match on truncated strings in a helper column. Less than
perfect to say the least.

Thanks for any help.

...best, Hash

 

[Harlan Grove]

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and
happened to notice that it skipped an obvious correct match.
After attacking that for too long a time, I found that
MATCH() won't handle strings longer than 255 characters.

....

OK. Since really just checking if the value of ref appears in
any cell in range, try the array formula

=MATCH(TRUE,SUBSTITUTE(range,ref,"")<>range,0)

which does work for arbitrarily long strings.

 

[Hash]

Dave -

Thank you for the steer to Debra's website. I used half of what I
learned there and and added it to Harlan's example. I liked using
ISNUMBER over ISNA. I like my IF's to resolve to the TRUE side ;-)

It's bookmarked. Thanks again.

....best, Hash

Debra Dalgleish has a workaround for using Data|Filter|Autofilter (It suffers
from the same malady.)
http://www.contextures.com/xlautofilter02.html#String

Can you extract the ref from the range into a helper column and then use that
helper column?

Or if you have lots of refs, could you extract them all and concatenate them
into that helper column?

All -

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and happened to
notice that it skipped an obvious correct match. After attacking that
for too long a time, I found that MATCH() won't handle strings longer
than 255 characters. The skipped cell satisfied ISTEXT(), FIND(),
SEARCH() and menu find efforts. The problem was that the skipped string
was 283 characters long, even though the query string started at 81.

1. Is this documented somewhere? It's not in my Excel function book,
and Google groups didn't readily reveal it.

2. What's the best workaround? Is there a UDF someone can point me to?
Right now I have over a thousand MATCHes to re-evalutate that might have
skipped some cases because the string length was too long. Right now I
plan to match on truncated strings in a helper column. Less than
perfect to say the least.

Thanks for any help.

...best, Hash

 

[Hash]

Harlan -

Thank you. To make it functionally equivalent (case-insensitive) I made
it

....SUBSTITUTE(LOWER(range),LOWER(ref),"")...

Works like a champ. Considering that this is a sliding range looking
for all matches by keying on the last match, I'd almost say it looks
like I know what I'm doing. Except for one thing. Why don't I have to
wrap the second range as

....<>LOWER(range)... too?

By inattention I didn't and now I don't understand why I didn't have to.
One-to-many arrays make my hair hurt. ;-)

Thanks again.
....best, Hash

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and
happened to notice that it skipped an obvious correct match.
After attacking that for too long a time, I found that
MATCH() won't handle strings longer than 255 characters.

...

OK. Since really just checking if the value of ref appears in
any cell in range, try the array formula

=MATCH(TRUE,SUBSTITUTE(range,ref,"")<>range,0)

which does work for arbitrarily long strings.

 

[Dave Peterson]

=substitute() is case sensitive.

Comparisons (=, <>) aren't.
="asdf"="ASDF"
will return True in a worksheet cell.

Harlan -

Thank you. To make it functionally equivalent (case-insensitive) I made
it

...SUBSTITUTE(LOWER(range),LOWER(ref),"")...

Works like a champ. Considering that this is a sliding range looking
for all matches by keying on the last match, I'd almost say it looks
like I know what I'm doing. Except for one thing. Why don't I have to
wrap the second range as

...<>LOWER(range)... too?

By inattention I didn't and now I don't understand why I didn't have to.
One-to-many arrays make my hair hurt. ;-)

Thanks again.
...best, Hash

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and
happened to notice that it skipped an obvious correct match.
After attacking that for too long a time, I found that
MATCH() won't handle strings longer than 255 characters.

...

OK. Since really just checking if the value of ref appears in
any cell in range, try the array formula

=MATCH(TRUE,SUBSTITUTE(range,ref,"")<>range,0)

which does work for arbitrarily long strings.

 

[Hash]

Dave -

Thanks.
...best, Hash

=substitute() is case sensitive.

Comparisons (=, <>) aren't.
="asdf"="ASDF"
will return True in a worksheet cell.

Harlan -

Thank you. To make it functionally equivalent (case-insensitive) I made
it

...SUBSTITUTE(LOWER(range),LOWER(ref),"")...

Works like a champ. Considering that this is a sliding range looking
for all matches by keying on the last match, I'd almost say it looks
like I know what I'm doing. Except for one thing. Why don't I have to
wrap the second range as

...<>LOWER(range)... too?

By inattention I didn't and now I don't understand why I didn't have to.
One-to-many arrays make my hair hurt. ;-)

Thanks again.
...best, Hash

I was doing wildcard matches [MATCH("*"&ref&"*",range,0)]and
happened to notice that it skipped an obvious correct match.
After attacking that for too long a time, I found that
MATCH() won't handle strings longer than 255 characters.
...

OK. Since really just checking if the value of ref appears in
any cell in range, try the array formula

=MATCH(TRUE,SUBSTITUTE(range,ref,"")<>range,0)

which does work for arbitrarily long strings.