Hi,

Is there a way to substitute letters from a word in a cell, with
predifined letters for the whole word?

Cell A1 contains the word. OLE.
Cells O1:O26 have the letters of the alphabet and cells P1:P26 have
the replacement letter.

I need a formula on cell A15 that will take the word OLE in cell A1
and replace all those letters with its match on cells P1:P26.

Thanks

Hi,

Not sure about a formula but here's a UDF. Alt+F11 to open VB editor, Right
click 'This workbook' and insert module and paste the code below in
in A15 enter the formula
=Subst(A1)


Function Subst(oldstring As String) As String
For x = 1 To Len(oldstring)
Mid(oldstring, x, 1) = WorksheetFunction.VLookup(Mid(oldstring, x, 1), _
ActiveSheet.Range("O1:P26"), 2, False)
Next
Subst = oldstring
End Function

Mike

" wrote:

Hi,

Is there a way to substitute letters from a word in a cell, with
predifined letters for the whole word?

Cell A1 contains the word. OLE.
Cells O1:O26 have the letters of the alphabet and cells P1:P26 have
the replacement letter.

I need a formula on cell A15 that will take the word OLE in cell A1
and replace all those letters with its match on cells P1:P26.

Thanks

Have a look in the help index for VLOOKUP
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
Hi,

Is there a way to substitute letters from a word in a cell, with
predifined letters for the whole word?

Cell A1 contains the word. OLE.
Cells O1:O26 have the letters of the alphabet and cells P1:P26 have
the replacement letter.

I need a formula on cell A15 that will take the word OLE in cell A1
and replace all those letters with its match on cells P1:P26.

Thanks

Hi,

Here is a formula approach:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1),LOOK UP(LEFT(A1),C1:C26,D1:D26)),MID(A1,2,1),LOOKUP(MID (A1,2,1),C1:C26,D1:D26)),RIGHT(A1),LOOKUP(RIGHT(A1 ),C1:C26,D1:D26))

I used the range C1:C26 and D1:D26 you should adjust those for you column O:P.

--
Thanks,
Shane Devenshire


" wrote:

Hi,

Is there a way to substitute letters from a word in a cell, with
predifined letters for the whole word?

Cell A1 contains the word. OLE.
Cells O1:O26 have the letters of the alphabet and cells P1:P26 have
the replacement letter.

I need a formula on cell A15 that will take the word OLE in cell A1
and replace all those letters with its match on cells P1:P26.

Thanks

Why repeat-post?
Thought Bernie's UDF solved it for you:
http://tinyurl.com/5wddmo
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---