=VLOOKUP(MID(A1,4,1),Sheet2!A$1:B$4,2,FALSE)

where Sheet2 A1-A4 contains A,B,C, D, etc letters
B1-B4 contains the corresponding numbers

Of course replace B$4 by the really last cell reference!

Regards,
Stefi


€žPIPERHAMMY€� ezt Ã*rta:

I am in the process of designing a spreadsheet and have came to a stumbling
block! I need excel to look at a cell and if the 4th digit is equal to A,B,C,
D, etc insert the corresponding number i.e. if excel looks at cell 'A1' the
number in that cell is 123B123 then in cell 'B1' I want it to enter 12345, if
it was 123C123 I would want it to insert 56789, if D 13465 would appreciate
any help. I have on the worksheet the list of letters and the list of
corresponding numbers so was wondering about some sort of lookup?

yours Aye

Bruce