Thanks Luke,

But unfortunately that's just an example. The Two Arrays to be interchanged
are of indefinite length and not only 3 cells each. That's the reason a UDF
is needed. Substitute() can't be used 20 times?

Please see if u can help.

"Luke M" wrote:

I'm assuming your data table starts in A2, first array is in B2:B4, and
second is in C2:C4. You could then use the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B$2,C$2),B$3, C$3),B$4,C$4)

Note that this is case sensitive though. You could either adapt your
original text, or have your output all in lower case with this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),B$2,C$ 2),B$3,C$3),B$4,C$4)

Copy down as desired.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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.