Can't get the right formula function.

[Jaemun]

Hi everyone,

I have text placed in A1 which is then I need to make some changes using
formula placed in A2. I've tried using Substitute and Replace but still
can't get the best result. Does it need a special functions to handle
formula? The table referrence located within range(A5:B11). The following
are an examples for these situations:-

A B
The quick brown fox jumps over the lazy dogs.
=The big fat cat jumps over the lazy monkeys.

fox cat
foxs cats
dog monkey
dogs monkeys
brown fat
quick big

Any help are appreciated.

Hopefully,
Jaemun.

 

[Guest]

Hi,
Try this User Defined Function (UDF). It assumes original words in
column A (starting on or after column 2) and replacements column B.

in B1 put "=ReplaceWords(A1)"


HTH


Function ReplaceWords(ByVal MyText As String) As String

Dim FirstRow As Long, Lastrow As Long, i As Long
Dim wdRng As Range

FirstRow = Cells(2, "A").End(xlDown).Row
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set wdRng = Range(Cells(FirstRow, "A"), Cells(Lastrow, "B"))

For i = 1 To wdRng.Count Step 2
MyText = Replace(MyText, Trim(wdRng(i)), Trim(wdRng(i + 1)), 1)
Next i

ReplaceWords = MyText

End Function

 

[Jaemun]

Hi Toppers,

Thank you. That was a nice one. It does handle the treate and works really
great!

Keep it up and wish you good luck. Cheer

Regards,
Jaemun.