Can u help me?

S

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
 
S

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Formula 4
VBA Code for converting Type-E UPC to Type-A (barcodes) 1
Converting ISBNs: 13 to 10 15
Need Formula 1
Loop 1
VLOOKUP Not working 3
Generating a random ID on a form. 1
Randomize from set of values 9

Top