Extract partial content from a single cell

[mildredtpeabody]

I have a cell that looks like this: PJL11100


and (1) I need to populate a cell that pulls the last 2 numbers (00). What formula will extract partial numbers?

(2) I also need another cell to tell me that because the 3rd to last number is 1 and because the last two numbers are 00 that the code should be populated as SAX.

Can anyone help me?

[mildredtpeabody]

Oops! The cell is: PJL11100

[Tony Valko]

Maybe these...

For the last 2 digits (extracted as TEXT):

=RIGHT(A2,2)

For the "SAX" test:

=IF(MID(A2,6,1)="1","SAX","")

[mildredtpeabody]

Thank you so much!! Now, some of the cells look like this: PLJ11200 or PLJ11300 and so on where 2 should equal R and 3 should equal M and so on. Can I make the formula test all of those possibilities easily so I can just past it down the column? Thanks!

[Tony Valko]

How many different variables are there?

If there are "many" (more than 3 or 4) then it would be easier to create a lookup table.

If there are only a "few" (3 or 4) then a nested IF would suffice.

Let me know.

[mildredtpeabody]

There are definitely more than 3 or 4.
For instance:

PJL11100 should be SAX because the last 1 is S and the 00 is AX
PLJ11208 should be RPX because the 2 is R and the 08 is LX
PLJ11317 should be MAX because the 3 is M and the 17 is PX

So the 6th spot is:
1 - S
2 - R
3 - M
4 - J
5 - N

While the 8th spot is:
0-4 - AX
5-7 - PX
8-9 - LX

Does that make sense?

You are so incredibly helpful, thank you so much!

[Tony Valko]

If you need to find codes for the last 3 digits then create a 2 column table with all of the combinations in the left column and the corresponding codes in the right column.

This data in a 2 column table in the range D2:E4:

100...SAX
208...RLX
317...MPX

A2 = PLJ11317

=VLOOKUP(--RIGHT(A2,3),D2:E4,2,0)

Result = MPX