Changing last character in a cell based on the character before it

**AZ-XL** · 04-25-2013, 01:05 PM

Hi,
I think it can be solved with lookup table. I listed your letters as a table and used that to change last table.
=LEFT(D2,LEN(D2)-1)&VLOOKUP(RIGHT(D2,1),$A$2:$B$8,2,0)

The only problem here is, there are 2 letets for letter S and vlookup will pick only one.

Uploading file here

**Falcongbz** · 04-25-2013, 01:30 PM

Thanks. Really close but in need that differentiation in the two different different S endings.

**AZ-XL** · 04-25-2013, 01:36 PM

What is the difference between them. I mena Z after S and N after S.
What is the logic behind this letters. Under what condition the last letter is Z or N?

**Falcongbz** · 04-25-2013, 01:48 PM

If the second last letter is "I" then the "S" should be converted to "N". If the second last letter is "B" then the "S" should be converted to "Z". Thanks.