check digit in the number

[santoshsapre]

Hi,


I have 12 digit ID number in column A in which 7th digit of no. is scheme code for number start from other than 9 and 5th digit is scheme code in number start from 9.


Pls note that ID number is in the txt format as it's downloading from other system.i have tried to convert in to number and apply =left formula but ID number star from 0 is not getting as scheme code changing to 6th digit.


scheme code is :

1 = SB

2 = CA

3 = CC


Required formula to get scheme name in column B.


ID number

006010200054

910010016914

037010100577

037010200031

910010035617

037010300041

910020009005

065010300011


sample xls file attached below:

http://dl.dropbox.com/u/66400357/ID%20number.xlsx

 

[Faseeh]

Hi santoshsapre,


In your workbook in D2 enter this formula and drag down:

=INDEX($G$2:$G$4,IF(LEFT(A2,1)="9",MID(A2,5,1),MID(A2,7,1)),0)

or see this worksheet:


http://dl.dropbox.com/u/60644346/ID%20number%20Reworked.xlsx


Regards,

Faseeh

 

[santoshsapre]

Faseeh,


great!! its work. Thanks a lot.

 

[Faseeh]

You are welcome Sir!


Faseeh