need excel to look for the 4th digit in a cell then insert new num

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Use VLOOKUP:


in B1:

=VLOOKUP(MID(A1,4,1),D1:E3,2,FALSE)

Columns D-E contains your lookup table as shown below

A B D E
123B345 56789 A 12345
B 56789
C 13645


HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List number of permutations for text nums 20
Can insert digital ink into Excel cell or a cell comment? 1
Excel Concatenate with line breaks but ignore blank cells 3
How add a digit to front of number in each cell in excexl column 1
display '2+2' as '4' in a cell? 3
Excel 2003: Cell rounds 16 digit numbers- How to advoid rounding? 1
NUM Error 1
How do I insert a cell without making a column? 1

Back
Top