• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Separate Passport no and nationality

Juniad

Juniad

Active 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:
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.
 
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.

67057
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,
 
You must log in or register to reply here.
Back
Top