Substitute Respective Entries In Arrays

[FARAZ QURESHI]

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.

 

[Luke M]

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.

 

[FARAZ QURESHI]

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.

 

[Jacob Skaria]

Dear Faraz

Hi again..Try the below.

1. When you are referring a table; you just need to refer that as a single
range.
2. The loop is for each Rows; and you refer the cells using .Cells(row,column)
3. You can use VBA TRIM function instead of Worksheetfucntion

With data in ColA ; your table in C14; try this in Col B, cell B1 =
=textclean(A1,$C$1:$D$4)

Function TEXTCLEAN(Str1 As String, r1 As Range) As String
Dim myC As Range
TEXTCLEAN = Str1
For Each myC In r1.Rows
TEXTCLEAN = Replace(TEXTCLEAN, myC.Cells(1, 1), myC.Cells(1, 2).Text)
Next myC
TEXTCLEAN = Trim(TEXTCLEAN)
End Function

If this post helps click Yes

 

[FARAZ QURESHI]

Thanx Jacob,

XClent solution. However, if A is to be replaced by C and C by D, the code
is leading to A being looped and replaced by D. Any idea how to restrict
replacement to be made once only?

 

[Jacob Skaria]

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

 

[FARAZ QURESHI]

XCLENT!

However, if the data contains an entry like JACOB and the replacing arrays
are:
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.

Any suggestions/recommendation?

Thanx again Pal!

 

[FARAZ QURESHI]

XCLENT!

However, if the data contains an entry like JACOB and the replacing arrays
are:
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.