Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can u help me?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can u help me?
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($A 2)="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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|