# need a formula to to find names of countries

#### mukhtarhi

##### New Member
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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?

In A1 cell
 1023 building road, street kingkong, Nepal

<colgroup> </colgroup><tbody>
</tbody>
In B1 cell
Code:
``=TRIM(MID(A1,FIND("^",SUBSTITUTE(A1,",","^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1,255))``

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

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.

Without any identifier I am unable to suggest any formula approach

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

will this help ?

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.

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

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

You are welcome. Thanls for providing feedback.

Replies
10
Views
594
Replies
3
Views
110
Replies
1
Views
268
Replies
0
Views
194
Replies
1
Views
717

1,196,412
Messages
6,015,116
Members
441,871
Latest member
lajervik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back