# replace multiple letter with multiple letters?

#### Harry0

##### Member
remember those decoder rings?

To have (example. since row 1 & 2 can be something else.)
row 1= 1 2 3 4...
row 2= a b c d...

if the rows were
cell
A B
123... abc...
231... bca...
159... aej...

What can formula in cell B be to get the answer?
thanks

#### bosco_yip

##### Excel Ninja
In B1, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"a"),2,"b"),3,"c"),4,"d"),5,"e"),6,"f"),7,"g"),8,"h"),9,"j")

Regards

Last edited:

#### Peter Bartholomew

##### Well-Known Member
A different approach is to use
= CONCAT( CHAR( 96+MID([@numbers], {1,2,3}, 1 ) ) )
where 'numbers' is a column within a table.
A uniform offset can be introduced using
= CONCAT( CHAR(96+ MOD( offset + MID([@numbers], {1,2,3}, 1 ), 26) ) )
or arbitrary letters can be returned from a lookup table 'characters' using
= CONCAT( INDEX( characters, MID([@numbers], {1,2,3}, 1 ) ) )
This last form would allow you to return 'j' rather than 'i' in place of 9.

Last edited:

#### Harry0

##### Member
Both are interesting.

The examples I gave was simple, but I was thinking of more like all of the 255 characters to be used in the mix. Char 1 to be changed for 237 or 1 with , or something else instantaneously.

I assume bosco_yip example I would need to type SUBSTITUTE 255 times.
And for Peter it seems close to what i mean but I am lost with it and fully dont get it to make it work for me.

AB (VARIOUS EXAMPLES)CDE...DZ
(Original)(changed)VALUE 1=
HEYÈÅÙ (CHAR ON OPPOSITE END as shown on side)CHAR SET 1=01...127
HEYIFU (CHARACTER NEXT TO IT)CHAR SET 2=128129...255
HEY^CN (CHARACTERS CUSTOM SET)VALUE 2=...ÿ

Last edited:

#### Peter Bartholomew

##### Well-Known Member
What version of Excel do you have? If you do not have the CONCAT function, you will need to output the converted characters to an array and either settle for that or use a whole string of '&'s. XLOOKUP would be good but LOOKUP will do. SEQUENCE would allow you to vary the lengths of the strings you wish to convert but there are ugly workarounds available in older versions of Excel.

What is needed is a sorted table of coded inputs alongside the desired outputs. The formulas are array-based so you most likely will need Ctrl+Shift+Enter to commit any formulas. Maybe this is closer?

#### Attachments

• 12.2 KB Views: 6

#### Harry0

##### Member
I have MS excel 2003 which cant handle concat to freeze.
kingsoft 2011 doesnt have concat.
I use the online of MS excel and google sheet which can handle concat but does not oddly seem to work.

well at least it helped someone else out.

#### Peter Bartholomew

##### Well-Known Member
2003! Wow, that doesn't leave much that I would care to use!
Does the attached work? I have removed the Table since that is Office2007.
I have never used the online versions so I don't know what is missing there.

#### Attachments

• 30 KB Views: 3