# Separate Passport no and nationality

#### Juniad

##### Member
Dear all.
plz help out to separate the pp no and nationality. Need result with formula for excel 2007

 ZC1234562PAKISTAN ZH1234563PAKISTAN ZH1234564PAKISTAN ZJ1234565PAKISTAN ZL1234566PAKISTAN ZP1234567PAKISTAN ZT1234568PAKISTAN ZU1234569PAKISTAN ZX1234570PAKISTAN 05844445NIPAL 07BB12345COMOROS 0123456ALGERIA A12345678EGYPT A12345678NIGERYA A2345678INDIA AA1234567BANGLADESH 2123456LEBANON

Last edited:

#### shrivallabha

##### Excel Ninja
With your posted sample, assuming the split occurs at numerical-alphabetical point following formula works which assumes that your data starts from cell A1
=LEFT(A1,LOOKUP(2,1/ISNUMBER(MID(A1,ROW(\$A\$1:INDEX(A:A,LEN(A1))),1)+0),ROW(\$A\$1:INDEX(A:A,LEN(A1)))))

Once passport number is found then finding the country name is fairly straight forward.
=SUBSTITUTE(A1,B1,"")

Ideal option would be to have a lookup list of countries to check against. It will return fairly accurately but it wont be able to handle the spelling issues in the posted sample e.g. NIPAL should be NEPAL and NIGERYA shall be NIGERIA.

#### Juniad

##### Member
its working perfectly thanks for helping.

#### bosco_yip

##### Excel Ninja
Another option to extract the alphanumeric value (pp no )

In B2, copied down :

=LEFT(A2,MATCH(1,INDEX(-MID(A2,ROW(\$1:\$99),1),0)))

Regards

#### Somendra Misra

##### Excel Ninja
Hi,

Just a different solution. I will make a table of countries, as it is fixed and does not change very often, as shown below.

I will give this table a name such as CountryList.

Than I will use below formula to extract the country name first.

=INDEX(CountryList[Country],MAX(IF(ISNUMBER(SEARCH(CountryList,A1)),ROW(CountryList[Country])))-1)
(Note this is an Array formula and must be entered with Ctrl+SHift+Enter)
(I have assume your data starts from A1)

Than I will use below formula to extract Passport number
=LEFT(A1,SEARCH(C1,A1)-1)

Ignore, if you find this method troublesome.

Regards,

#### ExcelSur

##### Member
Another option to extract the alphanumeric value (pp no )

In B2, copied down :

=LEFT(A2,MATCH(1,INDEX(-MID(A2,ROW(\$1:\$99),1),0)))

View attachment 67045

Regards
Hello Bosco,
Curious how to extra the country name. Thanks

#### bosco_yip

##### Excel Ninja
Hello Bosco,
Curious how to extra the country name. Thanks
To extract the country name

In B2 copied down :

=MID(A2,MATCH(1,INDEX(-MID(A2,ROW(\$1:\$99),1),0))+1,99)

Regards

#### Hany ali

##### Active Member
ExcelSur
why now you didn't press Like for bosco_yip's Answer ?!!!

#### ExcelSur

##### Member
To extract the country name

In B2 copied down :

=MID(A2,MATCH(1,INDEX(-MID(A2,ROW(\$1:\$99),1),0))+1,99)

Regards
Thanks Bosco