Excel formula that will insert a character from a table

[yonsei]

Hi,
trying to figure out how to create a formula that will take a 8 char. string and based on what the 3rd char is, will add the 9th character from a table. See file for example. So, for 22BUA32A, the result will be 13 different parts all ending with different chars. for the 9th chars.
thanks

[ChemistB]

You don't have "B" as a 3rd character in your table.

[yonsei]

ok, I added the "B" to the table, any ideas?

[FlameRetired]

Please provide more samples of desired output. This not all clear (to me).

[yonsei]

Ok, i've added sample output to my file

[Entomopithicus]

The pattern is not clear.
I can extract the third character from a string of any length using =RIGHT(LEFT(A17,3),1)
For "22BUA32A" the answer is B.

I do not see how to interpret "B" as the 9th character.

If B is the column for the table, then the next entry does not make sense as 22CUA32A would refer to column C where the 9th cell is empty.
Column C is "3rd character" but J is not the third character in any of the 8 character codes nor is it the third character in any of the columns.
I note that the number of characters in the columns decreases, but I have no idea why.

Often the best first step is to clearly define all the elements that make the pattern.

[yonsei]

ok, here is what i'm trying to do:
if the 3rd char in A17 is any of the chars in A2, then the output should look like B17 thru B29

[Entomopithicus]

That helps. Thank you.
My first "solution" is to unpack the string in A2 in J.
I can then use countif to see if there is a match in the third letter, and nest this in an if to test for that match and then join strings using & in cells J17 through J29.

The method is not generic, though it will work with any string of fewer than 8 letters in A2. I tested the other columns, and it seems to give the correct output. I added a B in cell G2 to see what would happen.

I intended to attach a file. In case it does not make it here is what I did.

Cell J2: enter =left(A2,1)
Cell J3: enter =right(left(a2,3),1)
Cell J4: enter =right(left(a2,5),1)
Keep going until cell j9 where you enter =right(left(a2,15),1)

Cell J17
=IF(B2="","",IF(COUNTIF(J$2:J$9,RIGHT(LEFT($A$17,3),1))>0,$A$17&B2,""))
Fill down to cell J29.



generic, though it will work with any string of fewer than 8 letters in A2. I tested the other columns, and it seems to give the correct output. I added a B in cell G2 to see what would happen.