need a formula to to find names of countries

[mukhtarhi]

Hi,

Can someone guide with a formula, just want to identify name of a country present in a line in a cell, for eg there is an address "1023 building road, street kingkong, Nepal" i have thousands lines like these in a column, need a formula which should give return value as "Nepal" since its the name of country, similarly there are different names of countries like Sri Lanka , China etc.

Kindly someone help.
Thanks.

 

[Aladin Akyurek]

Hi,

Can someone guide with a formula, just want to identify name of a country present in a line in a cell, for eg there is an address "1023 building road, street kingkong, Nepal" i have thousands lines like these in a column, need a formula which should give return value as "Nepal" since its the name of country, similarly there are different names of countries like Sri Lanka , China etc.

Kindly someone help.
Thanks.

Is the country always after the last comma?

Do you have a pre-set list of countries you are looking for?

 

[Sixthsense]

In A1 cell

1023 building road, street kingkong, Nepal

<colgroup> </colgroup><tbody>
</tbody>

In B1 cell

=TRIM(MID(A1,FIND("^",SUBSTITUTE(A1,",","^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1,255))

To Mask Error

=IFERROR(TRIM(MID(A1,FIND("^",SUBSTITUTE(A1,",","^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1,255)),"")

 

[mukhtarhi]

hi @Aladin Akyurek - it can any country in the world this is all i want to say, and not necessary there will be a comma .

• Thanks.
  

 

[mukhtarhi]

it can be any country in the world and not necessary seperated with a comma.
Thanks.

 

[Sixthsense]

Without any identifier I am unable to suggest any formula approach

 

[mukhtarhi]

hi @Sixthsense + @Aladin Akyurek

i have around these many names of countries " China, Zimbabwe, Ghana, Kenya, Sri Lanka, Nepal.

will this help ?

 

[Aladin Akyurek]

hi @Aladin Akyurek - it can any country in the world this is all i want to say, and not necessary there will be a comma .

• Thanks.
  

it can be any country in the world and not necessary seperated with a comma.
Thanks.

Assume that the country is the last bit in the string, preceeded by a space:

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

where A2 houses a string of interest.

If this does not suffice, try to find a table of country names on internet and invoke:

=LOOKUP(9.99999999999999E+307,SEARCH(COUNTRIES,A2),CCOUNTRIES)

where COUNTRIES stands for the the range housing the country names.

 

[mukhtarhi]

@ Aladin (=LOOKUP(9.99999999999999E+307,SEARCH(COUNTRIES,A2),CCOUNTRIES)

this formula works, Awsome ! iam extremely greatfull to you ! Cheers .

 

[Aladin Akyurek]

@ Aladin (=LOOKUP(9.99999999999999E+307,SEARCH(COUNTRIES,A2),CCOUNTRIES)

this formula works, Awsome ! iam extremely greatfull to you ! Cheers .

You are welcome. Thanls for providing feedback.