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
„(E-Mail Removed)” ezt *rta:
> 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
>
>
|
