=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