Can't get the right formula function.
 

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.

Can't get the right formula function.
 

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" wrote:

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.

Can't get the right formula function.
 

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.