1

I have a named ranged called streetTypes which coinsidentally contain a list of different street types eg street, road, rd, lane, drive, cres etc.

What would like to do is search a string for a match and display the starting position of the match - NOTE not just a boolean response.

Using the formula the below formula in col H (streetTypes named range in column J) the following result is displayed.

=SEARCH(streetTypes,G2)

enter image description here

While I am aware of why it is bugging out, I am unsure of how to fix.

Help anyone??

Improve this question

2 Answers 2

Reset to default
2

Try: =MAX(IFERROR(SEARCH(streetTypes,G2),"")) entered as an array formula (instead of hitting enter after typing the formula, type ctrl-shift-enter).

Use "Evaluate formula" (Formulas tab) to see how it works, but you'll get an array of numeric results or empty strings (one number for each term found) and then take the maximum to return the result found starting latest in your string. You could take the minimum to get the first in the string, but you probably want "Ave", not "st", in "53 West Seaside Ave".

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Great tip on using the min/max function. I ended up with the formula {=SEARCH(INDEX(streetTypes,MATCH(1,IF(SEARCH(streetTypes,G2),1,0),0)),G2)} which did the same job, just with way more code. To solve the max/min issue by preventing it form finding sub-strings, I abbreviations things like st, rd, ave pl at the end of the streetTypes array, with avenue, place, street, road at the beginning. (Because it matches on the first occurance)... Thanks again!
Ah, that's a better plan. You might also be interested in this: stackoverflow.com/questions/19481175/…
Oooohh. Very cool. You reckon it can be jigged to work with ranges? Ps:Thanks for your help. Just saw that you answered another one of my questions the other week :)
Actually, on closer examination, RXCount can be reworked by taking in a range, looping through it and converting it to a string. As for the other functions you have, I can see any really practical use for chucking ranges into them... Anyway... :)
0

I believe this cannot be done in excel as search function looks only for values in cells, not in a range. I have no knowledge of another such function which fulfills your needs.

Nevertheless, I suggest 2 solutions:

1- expand your "street type" range across columns, use them as headers and create a table. Use SEARCH function to get the starting positions of each street type. Use IFERROR function to return 0 instead of N/A, finally on the last column you can use the MIN function the get the lowest starting match position in a that row.

2- create a custom function with macros to loop through your street type range to find that value

good luck with both,

Improve this answer

1 Comment

Thanks for the feedback but see above answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.