Search for specific characters in specific positions in a cell then return a value

[TRICKD]

Hello ...

How do i search for specific characters in specific positions in a cell then return a value ?

E.g ... a cell contains a 10 character code ... if the first 2 characters = AB then return "text" and if the 5th character = C then return "text" and if the 9th character = D then return "text"
The returned "text" needs to be concatenated into one cell to give a converted value

I hope this makes sense and that you can help as ive looked online and not found anything of any use

Thanks

[Pete_UK]

Is "text" all the same, or should it be "text1", "text2" etc.?

What does "a converted value" mean?

Pete

[TRICKD]

Hello Pete , yes it would be "text1", "text2" etc ...

in reply to your question " What does "a converted value" mean? " ....... sorry , just my of saying the returned text would give me a different value to that of the source 10 character code

[Pete_UK]

Okay, try this, assuming your 10-character code is in A2:

=IF(LEFT(A2,2)="AB","text1","") & IF(MID(A2,5,1)="C","text2","") & IF(MID(A2,9,1)="D","text3","")

Hope this helps.

Pete

[TRICKD]

ok great ... so i now have this ... =IF(LEFT(A1,2)="Y1","TK1 TEMPLATE ","") & IF(MID(A1,6,1)="M","","") & IF(MID(A1,10,1)="N","1","") with a final returned value of TK1 TEMPLATE 1 ...

How would i alter the above to look for specific characters in varying positions in multiple 10 character codes ?

Example ... In the same worksheet :

A1 contains Y1N00M000N , the above formula looks at positions 1,2,6 & 10 to return TK1 TEMPLATE 1
A2 contains B8T00E000M , the formula needs to look at positions 1,2,9 & 10 to return TK8 TEMPLATE 2
A3 contains N4T0HM000R , the formula needs to look at positons 1,2 & 7 to return TK4 TEMPLATE 3

As you can see from the above i am trying to derive varying Template numbers based on certain characters held in different 10 character codes ..

Any help you can give would be great please ... Thanks