Need help on extracting name of city from a strings in a cell to another cell

[sukisukianto]

Hi..Good day to you all.

I work in the hospitality line and usually we have a Property Mangement System that take care of the reports for guest statistics, but the current PMS that I use right now does not support all the reports that I need. The workaround is to extract the data from the PMS to Excel. One of the report that I need help on is the Room Production by Guest City of Residency.

The way we enter those data is we enter the address in the profile page of guest inside the PMS, for example :

Address Line 1 : ABCD Road
Address Line 2 : No. 1A
City : City A
Postal Code : 123456
Country : Country A

The data above when extracted to Excel become :

ABCD Road, No. 1A, City A, 123456, Country A

Address Line 1 and Line 2 are mandatory to be fill up. That goes for City too, but the Postal Code does not always available to us so some guest might have this - other don't.

Now what I want to ask for guidance is..

How can I extract the "City A" from that strings of sentences and show it on another column? The current Formula that I used is

=TEXTJOIN(";"; TRUE; IF(COUNTIF(B3; "*"&$F$3:$F$17&"*"); $F$3:$F$17; ""))

But as you could see, the result shown was a mix of a few result due to the name of the road on Address Line 1 or Line 2 that coincide with name of another City. Am really in a bind now, and would really appreciate your enlightenment on this.

Thank you

Kind regards,

Suki

 

[Zot]

Thanks alot Zot, I've tried this code and it works well too.

Now that I have both solution for formulas and UDF. I'll mark this as Solved.

Thank you

Now I learned on how to use FILTERXML