XCLENT!

However, if the data contains an entry like JACOB and the replacing arrays
a
A a
O o
a X

The result comes out to be:

JaCOB instead of JaCoB.

In other words, only A was replaced with "a". Although "a" was not replced
further to X the O was not replaced as well.



"Jacob Skaria" wrote:

Faraz, to stop after the first replacement try this

Function TEXTCLEAN(Str1 As String, r1 As Range) As String
Dim myC As Range
For Each myC In r1.Rows
If InStr(1, Str1, myC.Cells(1, 1), vbTextCompare) 0 Then
TEXTCLEAN = Replace(Str1, myC.Cells(1, 1), myC.Cells(1, 2).Text)
Exit For
End If
Next myC
TEXTCLEAN = WorksheetFunction.Trim(TEXTCLEAN)
End Function



--
If this post helps click Yes
---------------
Jacob Skaria


"FARAZ QURESHI" wrote:

I often come across to make several changes in long data columns. Find &
Replace would completely make the changes but in a final form.

Could any of you experts please help me in designing a UDF so as to make all
the characters/combinations of the same, as inserted in one array, be
replaced with the respective entries in another.

Example is as below:

The Data Column is like:
First
Second
Third
Fourth

First Array has entries like:
ir
s
d

Second Array has respective entries as follows:
aa
bb
cc

What sort of UDF would, when applied upon the data, would lead to the
following result:
Faabbt
bbeconcc
Thaacc
Fourth

I have tried the following code but can't seem to figure out a proper
looping so as to refer to the entries in the second range:

Function TEXTCLEAN(Str1 As String, r1 As Range, r2 As Range) As String
Dim myC As Range
TEXTCLEAN = Str1
For Each myC In r1
TEXTCLEAN = Replace(TEXTCLEAN, myC.Value, <<How 2 refer r2?)
Next myC
TEXTCLEAN = Application.WorksheetFunction.Trim(TXTCLN)
End Function

Thanx in advance.