Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Wildcard MATCH() breaks on long (?) strings
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 |
#3
|
|||
|
|||
wrote...
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. |
#4
|
|||
|
|||
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 In article , Dave Peterson wrote: 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? wrote: 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 |
#5
|
|||
|
|||
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 In article , "Harlan Grove" wrote: wrote... 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. |
#6
|
|||
|
|||
|
#7
|
|||
|
|||
Dave -
Thanks. ...best, Hash In article , Dave Peterson wrote: =substitute() is case sensitive. Comparisons (=, <) aren't. ="asdf"="ASDF" will return True in a worksheet cell. wrote: 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 In article , "Harlan Grove" wrote: wrote... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clock | Excel Worksheet Functions | |||
Please how do do this in vba | Excel Worksheet Functions | |||
Find nth instance of a character in a string | Excel Discussion (Misc queries) | |||
Filter long Text strings | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |