Substitute letters by others in a word

[HammerJoe]

Hi,

Someone sent me one of those silly puzzles where the I need to
unscramble the word.

I want to solve this puzzle.
Cells A1 to L8 contains each one scrambled word.

cells O126 have the alphabetical letter on the first column and
whatever I want on the second.

I need a formula on A15 that will take the word on A1 and replace all
the letters in the word using my key.

Suppose the letter in A1 is VI.
Suppose P22 for the letter V is T, for P9 for the letter I is O.
The result on cell A15 would then be TO.

I hope its clear.
Thanks

 

[Sheeloo]

I don't think this can be done using a formula but I will love to be proved
wrong...

 

[Bernie Deitrick]

HJoe,

You could use a custom UDF: put the code below into a regular codemodule in
your workbook, then use it like this in cell A15

=NewWord(A1,$O$1:$P$26)

Then copy that cell to A15:L22

HTH,
Bernie
MS Excel MVP


Function NewWord(c As Range, Subs As Range) As String
Dim i As Integer
NewWord = ""
For i = 1 To Len(c.Value)
NewWord = NewWord & Application.VLookup( _
Mid(c.Value, i, 1), Subs, 2, False)
Next i
End Function

 

[ShaneDevenshire]

Hi,

Here is the basic idea for a LONG formula, how long depends on how long your
longest word can be.

=LOOKUP(MID(A1,1,1),$O$1:$O$26,$P$1:$P$26)&IF(LEN(A1)>=2,LOOKUP(MID(A1,2,1),$O$1:$O$26,$P$1:$P$26),"")&IF(LEN(A1)>=3,LOOKUP(MID(A1,3,1),$O$1:$O$26,$P$1:$P$26),"")

This handles 3 letter word, just add another
&IF(LEN(A1)>=3,LOOKUP(MID(A1,3,1),$O$1:$O$26,$P$1:$P$26),"") and change both
3's to 4's. Repeat this as many times as necessary.

If this helps, please click the Yes button.

 

[Ron Rosenfeld]

Hi,

Someone sent me one of those silly puzzles where the I need to
unscramble the word.

I want to solve this puzzle.
Cells A1 to L8 contains each one scrambled word.

cells O126 have the alphabetical letter on the first column and
whatever I want on the second.

I need a formula on A15 that will take the word on A1 and replace all
the letters in the word using my key.

Suppose the letter in A1 is VI.
Suppose P22 for the letter V is T, for P9 for the letter I is O.
The result on cell A15 would then be TO.

I hope its clear.
Thanks

Here's one way.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this **array-entered** formula.

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula:

=MCONCAT(TRANSPOSE(OFFSET(P1,MATCH(MID(A1,INTVECTOR(LEN(A1),1),1),$O$1:$O$26,0)-1,0)))

--ron