CODEWITHSUNDEEP
searchexcel-formula
Chris
Chris

Reputation: 798

Using an array of search terms to search string in Excel

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??

Upvotes: 1

Views: 214

Answers (2)

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

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

Upvotes: 2

Trevize Daneel
Trevize Daneel

Reputation: 111

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,

Upvotes: 0

Related Questions