Returning text from the end of a string

[sobeitjedi]

Hi In column "L" I have a list of cities/countries:

Toronto, Ont., Canada
Montreal, Que., Canada
Toronto, Ont., Canada
Manchester, England, United Kingdom
New York City, New York, United States
Frankfurt, Germany
Abuja, Nigeria
Frankfurt, Germany
Manchester, England, United Kingdom
Doha, Qatar
Hanoi, Vietnam
Istanbul, Turkey
Yaounde, Cameroon
Istanbul, Turkey
Manchester, England, United Kingdom

In Column "M", I want a formula, to put the last part of this field after the comma, but I can't get the syntax right, particularly when some rows have more than one comma.

Please help!

 

[tyija1995]

Hey, you want the text AFTER the last comma in a string?

In M2:
=TRIM(MID(L2,1+SEARCH("|",SUBSTITUTE(L2,",","|",LEN(L2)-LEN(SUBSTITUTE(L2,",","")))),LEN(L2)))

Where I assume L2 is the first text "Toronto, Ont., Canada"

Alternatively (shorter formula):

=TRIM(RIGHT(SUBSTITUTE(L2,",",REPT(" ",LEN(L2))),LEN(L2)))

 

[njimack]

=trim(right(a1,len(a1)-find("#",substitute(a1,",","#",len(a1)-len(substitute(a1,",",""))))))

 

[sobeitjedi]

Would never have worked that out! Thank you so much, saved me a lot of time.