PDA

View Full Version : Attached alpha numeric based on criteria



tlchan
09-25-2008, 08:37 AM
I wish to add alphanumeric value to existing cell in column E to form a standard fixed length serial No based on account no in column B. The alpha numeric number to be determined by 2nd to 6th digit of the account number. However if there is account number started with zero which not appear on the 1st digit, the 1st number in alphanumeric no will start with 0 e.g ENV0XXXX .

I hope somebody can assist me over this. Thank you

Attached the sample workbook showed what I expect cell value in column E

Bob Phillips
09-25-2008, 08:53 AM
="ENV"&IF(LEN(B2)=12,MID(B2,2,6),"00"&MID(B2,1,4))&TEXT(E2,"00000000")

rbrhodes
09-25-2008, 03:29 PM
Hi,

I tested the formula on the OP's example sheet and got two errors (17 digit numbers intead of 16 digit ones. i think the correction is a single zero as in:

="ENV"&IF(LEN(B2)=12,MID(B2,2,5),"0" &MID(B2,1,4))&TEXT(E2,"00000000")

Also it should be noted that it has 2 restrictions:

1) SDM# Maximum 8 digits

2) Acct # Minimum 4 digits

I don't imagine either of the above will come into play...

tlchan
09-25-2008, 05:56 PM
Fantastic!

Thanks Xld and rbrhodes for your quick solution.


The 2 restrictions mentioned apply in my case where acct # should have 5 digit. Rbrhodes refined solution is what I expected for.


Cheers