Help if 4 is the 4th character, replace it with

[CeLxIcFeTlON]

Hi,

Good afternoon,

Can someone help me, i have a code of 22 digits, this represents a lot of staff ie.

if the 4th character is "4" means DE SALE if "5" means AT SALES
if the 5th character is "6" means express
etc etc.
the meaning of each number is saved on a different sheet.

which formula is best to use

i have attached a example.

your help is much appreciated

Clif

[AliGW]

In B2 copied across and down:

=IFERROR(INDEX(ATT!$B$2:$W$4,MATCH(--MID($A2,COLUMN(A1),1),ATT!$A$2:$A$4,0),COLUMN(A1)),"")

[davsth]

your example does not represent your data you have 0-9 as valid numbers and your strings are 21 characters long!

Is the attached along the lines of what you want?

[CeLxIcFeTlON]

Thank you so much, it worked much appreciated

[AliGW]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[CeLxIcFeTlON]

Hi if you have time can you explain the logic please, as on 3rd 4th 5th didn't work. I've attached a second excel maybe i'm doing something wrong

[AliGW]

You didn't extend the lookup ranges!

=IFERROR(INDEX(ATT!$B$2:$W$11,MATCH(--MID($A2,COLUMN(A1),1),ATT!$A$2:$A$11,0),COLUMN(A1)),"")

[CeLxIcFeTlON]

its works thank you all

[CeLxIcFeTlON]

thank you so much

[AliGW]

My pleasure.