generating auto alpha-numeric in one column based upon text in another column

Dear Frends,

I have recently register myself in excelforum. I am preparing a database of our ground staff where I want to make a separate ID based upon the name of the staff in alphabetic order. My question is can we add a serial number which is of alphanumeric character and the ID starts with the Alphabet and continue with numeric value an example is as follows:

Column A ColumnB
Serial No. Name

A001 Ajay Sharma
A002 Anil Kumar
A003 Asutosh Chand
B001 Beerbal Kumar
B002 Budhu Ram
C001 Chandan Singh
D001 Deaya Shanker Tiwari


I want the ID number enters automatically based upon First letter of Name and the numeric value to be continuous. And as the first letter of Name change from "A" to "B" the ID number should starts from B001 and continue to increase until next alphabet and so on.


Please suggest me the creation of suitable formula using appropriate function.

This formula doesn't solve my purpose, although it generate alphanumeric number but not as per requirement. It resulted N001, N002 and so on....but I want number to be synchronous with the first letter of the name A001 A002........B001 B002.....like that.

Thanks a million, my this purpose is solved. However, what if I want that ID number should entered automatically:

I tried the simple way but it entered only first alphabet on the name. The formula I used is:

=If(IsBlank(B3)," ",B2+1)

How can we merge the two formulas?

Thanks ones again, its done.

Sorry.........