• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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")

66354

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.

66358
 
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?

66360
 

Attachments

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

Top