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

 

[sukisukianto]

Update :

Trying another approach with :

=TRIM(LEFT(RIGHT(SUBSTITUTE(B3;",";REPT(" ";250));500);250))

Solve some of the addresses, but when the address comes with the Postal Code then it will show the Postal Code instead of the City. Any suggestion or added formula in the existing formula would be greatly appreciated.

Thank you

Kind regards,

Suki

 

[Zot]

I think it would be simpler using UDF

Function GetCity(cell As Range) As String

Dim ArryTxt() As String

ArryTxt = Split(cell, ",")

If Not IsNumeric(ArryTxt(UBound(ArryTxt) - 1)) Then
    GetCity = ArryTxt(UBound(ArryTxt) - 1)
Else
    GetCity = ArryTxt(UBound(ArryTxt) - 2)
End If

End Function

 

[Zot]

Indon.xlsx
	A	B	C
7
8	Data Extract from PMS	Result with Current Formula	Desired Result
9	Jl. Tukad Buana III / 28, Batu Kandik Padang Sambian Kaja, Denpasar, Indonesia	Batu;Denpasar;Padang	Denpasar
10	HOS Cokroaminoto 207 RT11 RW 04 Kel. Tegalrejo, Yogyakarta, 65215, Indonesia	Tegal;Yogyakarta	Yogyakarta
11	Jl. Camar V AG 36, Pondok Aren, Tangerang Selatan, Indonesia	TangerangjTangerang Selatan	Tangerang Selatan
12	Citra Garden City 6 Fines Homes Blok L5/38, Tegal Alur, Jakarta, 13410, Indonesia	Tegal;Jakarta	Jakarta
13
Sheet1

Cell Formulas
Range	Formula
C9:C12	C9	=GetCity(A9)

 

[Z51]

Your value in B4 seems to be missing a comma, perhaps after "Kel." should be "Kel," since both address lines are required, there should be two commas preceding the City field.

=MID(B3,FIND("^",SUBSTITUTE(B3,", ","^",2))+2,FIND("^",SUBSTITUTE(B3,", ","^",3))-FIND("^",SUBSTITUTE(B3,", ","^",2))-2)

 

[sukisukianto]

Your value in B4 seems to be missing a comma, perhaps after "Kel." should be "Kel," since both address lines are required, there should be two commas preceding the City field.

=MID(B3,FIND("^",SUBSTITUTE(B3,", ","^",2))+2,FIND("^",SUBSTITUTE(B3,", ","^",3))-FIND("^",SUBSTITUTE(B3,", ","^",2))-2)

Sorry..That address line seems to be only filled up on the Address Line 1 with the Line 2 empty that was why there is only 1 comma. ( Kel. is actually an abbreviation of the word Kelurahan which means ward / district or some sort )

I have tried your formula, it works well on almost all of the addresses except those with only Address Line 1 filled up. Although this could be prevented by making sure that both Address Line 1 and 2 are properly filled up, but just wonder whether there is an additional added formula that could still give the same result whether both address is fill up or even with just one of them ( sorry..I know am being greedy here ). Since I would need to backdated this report from January and thought that there might be a lot that was not properly filled up.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2		Data Extract from PMS	Result with Current Formula
3		Jl. Tukad Buana III / 28, Batu Kandik Padang Sambian Kaja, Denpasar, Indonesia	Denpasar
4		HOS Cokroaminoto 207 RT11 RW 04 Kel. Tegalrejo, Yogyakarta, 65215, Indonesia	Yogyakarta
5		Jl. Camar V AG 36, Pondok Aren, Tangerang Selatan, Indonesia	Tangerang Selatan
6		Citra Garden City 6 Fines Homes Blok L5/38, Tegal Alur, Jakarta, 13410, Indonesia	Jakarta
Data

Cell Formulas
Range	Formula
C3:C6	C3	=FILTERXML("<k><m>"&SUBSTITUTE(B3,",","</m><m>")&"</m></k>","//m[.!=number()][position()=last()-1]")

 

[sukisukianto]

Indon.xlsx
	A	B	C
7
8	Data Extract from PMS	Result with Current Formula	Desired Result
9	Jl. Tukad Buana III / 28, Batu Kandik Padang Sambian Kaja, Denpasar, Indonesia	Batu;Denpasar;Padang	Denpasar
10	HOS Cokroaminoto 207 RT11 RW 04 Kel. Tegalrejo, Yogyakarta, 65215, Indonesia	Tegal;Yogyakarta	Yogyakarta
11	Jl. Camar V AG 36, Pondok Aren, Tangerang Selatan, Indonesia	TangerangjTangerang Selatan	Tangerang Selatan
12	Citra Garden City 6 Fines Homes Blok L5/38, Tegal Alur, Jakarta, 13410, Indonesia	Tegal;Jakarta	Jakarta
13
Sheet1

Cell Formulas
Range	Formula
C9:C12	C9	=GetCity(A9)

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

 

[sukisukianto]

How about

Fluff.xlsm
	A	B	C
1
2		Data Extract from PMS	Result with Current Formula
3		Jl. Tukad Buana III / 28, Batu Kandik Padang Sambian Kaja, Denpasar, Indonesia	Denpasar
4		HOS Cokroaminoto 207 RT11 RW 04 Kel. Tegalrejo, Yogyakarta, 65215, Indonesia	Yogyakarta
5		Jl. Camar V AG 36, Pondok Aren, Tangerang Selatan, Indonesia	Tangerang Selatan
6		Citra Garden City 6 Fines Homes Blok L5/38, Tegal Alur, Jakarta, 13410, Indonesia	Jakarta
Data

Cell Formulas
Range	Formula
C3:C6	C3	=FILTERXML("<k><m>"&SUBSTITUTE(B3,",","</m><m>")&"</m></k>","//m[.!=number()][position()=last()-1]")

Thank you Fluff, yours works as well. Have tried a few combination of the Address with and without Line 2, with and without Postal Code and it still comes back to the City name. Thanks so much

 

[Fluff]

Glad we could help & thanks for the feedback.