Match and subtitute multiple texts in a simpler way

[improvet]

Hi

I have for example a line of text like this: "abcdef" and I want to vlookup two letters at a time to replace the whole text with something else.

I have a lookup table like this in column A and B:
ab = 1
cd = 2
de = 3
ef = 4
and so on

So function input is this: "abcdef" and output would be "1234"

at first I tried "=VLOOKUP(MID(C1,{1,3,5,7},2),A:B,2,0) but it didnt like this way of doing it so it just returned a match on the first match from MID.

Do you guys know a better solution than brute-forcing by repeating the same formula for each start position in mid?
(=VLOOKUP(MID(C1,1,2),A:B,2,0)&VLOOKUP(MID(C1,3,2),A:B,2,0) etc)

 

[Special-K99]

1) Is each line of text ALWAYS 6 characters in length?

2) Can you confirm that your lookup table is indeed
ab
cd
de
ef

and not

ab
cd
ef

If your table is correct as you originally specified then it raises the question why there is no "bc" lookup in the table? Should there be?

3) You say your lookup table is

ab
cd
de
ef
and so on

Can you elaborate the "and so on", ie provide the full lookup table with accompanying values?

4) I'm starting to think a VBA solution may be better...

 

[Special-K99]

Ok, here's one way of doing this.

In A1 place the text, e.g. abcdef
in B1
=MID($A1,COLUMNS($B1:B1),2)
and copy across the columns for n columns where n is the longest piece of text you have divided by 2.

Then perform a IFERROR(VLOOKUP()..."") for each result in column B to (the last column), ie for the results of the formula above placing the results in each column.
So you should now have running from lef to right...

Column A the text
Col B... a pair of letters from column A (in this case ab bc cd de ef f)
Col ??... either a null value or the numeric result of the VLOOKUP (in this case, 1, , 2, 3, 4)

Then just concatenate the ciolumns of the numeric/null result to provide the final answer, in this case 1234