Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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 C1:D4; 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 --------------- 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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" wrote: 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 C1:D4; 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 --------------- 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. Any suggestions/recommendation? Thanx again Pal! "Jacob Skaria" wrote: 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 C1:D4; 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 --------------- 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Respective Entries In Arrays
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When i am at a cell, how can i highlight respective row and column | Excel Discussion (Misc queries) | |||
IF + respective Validation Dropdown List | Excel Worksheet Functions | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
Looking up data in a column, then returning values of respective row | Excel Discussion (Misc queries) | |||
In Bar Chart, can we display both figures and their respective %a. | Excel Discussion (Misc queries) |