Can u help me?

[satish.tata]

Hi ,

I have a one excel sheet with the column called OMANG ID/PASSPORT
NUMBER, this column contains Numeric & alfha numeric values(5-12
digits value).
Omang should have 9 digits & fifth digit should be 1 or 2.otherwise it
is passport.Passport may have numeric or alphanumeric.

First I have to check the Numeric or Alfanuremic.If it is numeric with
5 digits thn i append the four zero's in front of the tht 5 digit
no( to get the 9 digit)

1. If It is Numeric with 9 digits thn it is Omang, other wise(means
alphanumeric or more thn 9 digits) it is Passport.This i specify in
one column called OMANG/PASSPORT.

2.Depending on this OMANG ID/PASSPORT NUMBER column ,if it is numeric
value with 9 digits,i am going to specify tht member's Sex(male 0r
female) in another column called GENDER, in tht 9 digits, 5th digit
tells the male or female,if 5th digit is 1 thn male ,if it is 2
female.

For this can u help me...


SATISH

 

[Stefi]

Say OMANG ID/PASSPORT NUMBER is column A

You need a helper column B for transforming column A values with this formula
=IF(AND(ISNUMBER(VALUE($A2)),LEN($A2)=5,OR(LEFT($A2)="1",LEFT($A2)="2")),"0000"&$A2,$A2)

OMANG/PASSPORT column C formula
=IF(AND(ISNUMBER($A2),LEN($A2)=9,OR(MID($A2,5,1)="1",MID($A2,5,1)="2")),"OMANG","PASSPORT")

GENDER column D formula:
=IF(AND(ISNUMBER(VALUE($B2)),LEN($B2)=9,MID(B2,5,1)="1"),"male","female")

Regards,
Stefi


„[email protected]†ezt írta: