Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute letters by others in a word
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 O1:P26 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute letters by others in a word
I don't think this can be done using a formula but I will love to be proved
wrong... " wrote: 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 O1:P26 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute letters by others in a word
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 wrote in message ... 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 O1:P26 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute letters by others in a word
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. -- Thanks, Shane Devenshire " wrote: 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 O1:P26 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute letters by others in a word
On Sat, 25 Oct 2008 19:19:37 -0700 (PDT), "
wrote: 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 O1:P26 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,INTVECTO R(LEN(A1),1),1),$O$1:$O$26,0)-1,0))) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup using partial word, first 4 letters | Excel Worksheet Functions | |||
how do I reduce the spaces between letters in a word? | Excel Discussion (Misc queries) | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
How do change a column of data in capitol letters to small letters | Excel Discussion (Misc queries) | |||
How do you sort words in Excel by the number of letters in a word | New Users to Excel |