Extracting Specific Word Using Mid/Find

Greetings!

I would like to ask what formula should I use to extract specific word by referencing a word after the word I want to extract. given with this example.

Billing Address.xlsx

the reference is the word "PHILIPPINES"
then I wanted to extract the province for example "METRO MANILA" in Column C, which is the word before the reference "PHILIPPINES"
then I wanted to extract the city/town for example "TAGUIG" in Column B, which is the word before the province.

by using the function MID and FIND how should I create a formula to extract those words. Appreciate your feedback, tnx.

Hi,

you could write a list of provinces in - for example- G3:G50

In C3:

=INDEX($G$3:$G$50,MATCH(1,SEARCH("*"&$G$3:$G$50&"*",A3),0))

or as Excel 2007 user something like:

=IFERROR(INDEX($G$3:$G$50,MATCH(1,SEARCH("*"&$G$3:$G$50&"*",A3),0)),"No match")

The formula must be confirmed with control+shift+enter and then copied down.

Just for sharing a first option

Regards

Hi Canapone, thank you for the suggestion, your approach may be applicable to the province column only, since in my database I only had 92 provinces in total, but doing it in column of city/town, it may respond too long since there are total of 900 cities in the database. Is there any other option to extract the town/city?

Hi,

are cities one word only?

Example not "CAPE TOWN" or "LAS VEGAS".

Regards


EDIT: sorry I had not seen QUEZON CITY.

In this case the only easy option is -again- matching a list of cities.

Waiting for better solutions.

Regards

Unfortunately, it's not, there are some cities with three words on it.

by the way, can you check what's wrong with the formula I used, the index is just in another sheet, and all of its results are "No Match"

AddFin 1.0.xlsm

this is the original workbook I am working for.

Hi,

i'll take a look at the formula in the afternoon, I'm back in two hours.

Meanwhile I hope you gett better solutions.

See you later

Ok, I'll be waiting for it.

at this time of need, your approach may be the better solution to work for.

hmm, I think I got it now, looking thoroughly from the formula you've given, I made the formula by mistake, it is now working and resulting to a right output.

{=IFERROR(INDEX(ProTown!A$2:A$83,MATCH(1,SEARCH("*"&ProTown!A$2:A$83&"*",I3),0)),"No match")}

Hi,

exactly.
You have to complete the list of provinces to avoid "no match"'s, if you cannot download from internet a list of the cities and use the same formula, you could prepare a smaller list of cities composed by two or more strings (see the example attached in column B) and use again IFERROR

=IFERROR(INDEX(ProTown!$B$2:B$83,MATCH(1,SEARCH("*"&ProTown!$B$2:$B$83&"*"I3),0)),TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I3," "&UPPER(K3),"")," "&L3,"")," "&"PHILIPPINES","")," ",REPT(" ",100)),100)))

Again control+shift+enter

I'm sure there are better and more elegant solutions.

Regards

hi Canapone, thank you for your help. For the meantime I will be using your formula. It's helping me in my project anyway.

If you wouldn't mind, do you know how to use this formula in VBA? I just wanted to add this to the code, I am inserting this to the last part. where,

ActiveCell.Offset(0, 8).value = {=IFERROR(INDEX(ProTown!A$2:A$83,MATCH(1,SEARCH("*"&ProTown!A$2:A$83&"*",I3),0)),"No match")}

I wanted to put the output of the formula to the specified cell range not to put the formula itself.

I got it, I am using this formula now inserted in a vba code.

ActiveCell.Value = Evaluate("IFERROR(INDEX(ProTown!A$2:A$83,MATCH(1,SEARCH(""*""&ProTown!A$2:A$83&""*"",OFFSET(" & ActiveCell.Address(0, 0) & ",0, -2)),0)),""No match"")")

Thank you for your help Canapone.

Hi Canapone,
can you identify what is the cause why it always giving result of "No Match" in this formula? I think the formula is correct but I don't know why there's no match.

AddFin 1.0.xlsm

Hi,

you should create a list of cities in a single column (B2:B823) and in the INDEX and in SEARCH change B2:E107 in B2:B823.



The formula should work.

Regards

hi Canapone, thank you for your advise, unfortunately upon observing, this formula cannot be used for city, since there are cities that are the same as province, it will give a result which searches for the province first, is there a way that the match/search will start from the left so that it will find first the city before the province? let's say for example, i have here the address:

ZONE 7 SITIO KAUNLARAN WESTERN BICUTAN WESTERN TAGUIG METRO MANILA PHILIPPINES 1630

what I need is the search will start from the left, so that it could find first the city "TAGUIG" instead of finding "MANILA", the result of the formula in this address is "MANILA" since it is also included in the list of cities.

Hi,

I don't know if it's the best solution: try to move at the bottom of the list of cities the provinces/cities in order to create a sort of hiearchy different from the natural alphabetical order.

Hope it's a little help

Regards

Hi Canapone, ok I'll try your suggestion. thank you for the suggestion.