Find and Replace Array UDF issue

[mohamadmh]

Hi all,

Thank you in advance for your help. I am working on a function in cell B to find and replace text in cell A. I still need cell A is a field I and cell B is a classification of that ID. The UDF I have only replace 1 (the last string in example below Date) of the three strings I want to replace. Any suggestions.

For example:
Cell A Cell B Output
Field 3, Field 4, Field 8, Field 9 ID, Type, ISIN, Date

Function Replace3(expression As String) As String
Dim strWhat As String, strRep As String
Dim arrWhat As Variant, arrRep As Variant
Dim i As Long

On Error GoTo Replace3_Error

strWhat = "Field 3, Field 4, Field 8, Field 9"
strRep = "ID ,Type, ISIN, Date"

arrWhat = Split(strWhat, ",")
arrRep = Split(strRep, ",")

For i = LBound(arrWhat) To UBound(arrWhat)
Replace3 = expression
Dim position As Long: position = InStr(1, expression, arrWhat(i))
Replace3 = Left(expression, position - 1)
Replace3 = Replace3 & Mid(expression, position + Len(arrWhat(i)))
Replace3 = Left(Replace3, position - 1) & _
arrRep(i) & _
Right(Replace3, Len(Replace3) - position + 1)
Next i
Exit Function

Replace3_Error:
Replace3 = expression
End Function

 

[Andrzejek]

Your requirements and your example are not very clear.

It would be easier if you would state, for example:[tt]
In cell A1 I have: ABCD
In cell B1 I have: 123 XYZ
In cell C1 I would like to get: [red]???[/red][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mohamadmh]

Hi Andy,

Thank you for your response. Apologies for not being clear:

In Cell A1 I Have: Field 3, Field 4, Field 8, Field 9, Field 10
In Cell B1 I would like to get: ID, Type, ISIN, Date, Field 10

I want to only replace a subset of fields in cell not all. Hope that is clearer. Thank you in advance for your help.

 

[Andrzejek]

Hope that is clearer" - no, it is not clearer :-(
[tt]
In Cell A1 I Have: Field 3, Field 4, Field 8, Field 9, [blue]Field 10[/blue]
In Cell B1 I would like to get: [red]ID, Type, ISIN, Date[/red], [blue]Field 10[/blue]
[/tt]
Where are you getting [red]ID, Type, ISIN, Date[/red] from? To place all of that in B1
I assume you want to grab last piece from A1 ([blue]Field 10[/blue]) and place it as a last piece in B1 ([blue]Field 10[/blue]).

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

By the way, if you want to play with creating UDF (User Defined Function) by coding in VBA, that's OK

But if you have a workbook with a lot of rows in column A with the Comma delimited data, you should consider using [blue][tt]Data - Text to Columns[/tt][/blue] (here is how to do it) to convert your one column A into multiple columns containing all your individual fields and use Excel's build-in functionality to accomplish whatever you need. No VBA coding needed.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mohamadmh]

Hi Andy,
i am using the following UDF, but it is not replacing all values only one

Function Replace3(expression As String) As String
Dim strWhat As String, strRep As String
Dim arrWhat As Variant, arrRep As Variant
Dim i As Long

On Error GoTo Replace3_Error

strWhat = "Field 3, Field 4, Field 8, Field 9, Field 10"
strRep = "ID ,Type, ISIN, Date"

arrWhat = Split(strWhat, ",")
arrRep = Split(strRep, ",")

For i = LBound(arrWhat) To UBound(arrWhat)
Replace3 = expression
Dim position As Long: position = InStr(1, expression, arrWhat(i))
Replace3 = Left(expression, position - 1)
Replace3 = Replace3 & Mid(expression, position + Len(arrWhat(i)))
Replace3 = Left(Replace3, position - 1) & _
arrRep(i) & _
Right(Replace3, Len(Replace3) - position + 1)
Next i
Exit Function

Replace3_Error:
Replace3 = expression
End Function

 

[Andrzejek]

I see your code, and I do know it is not working as you wanted.
But you still did not answer my question - where ID, Type, ISIN, Date is coming from from?

All what you have available in your Replace3 function is 'expression' As String, which - I assume - is what's coming from cell A1, which is "Field 3, Field 4, Field 8, Field 9, Field 10"

And for your Function to work, you canNOT have any hard-coded data, like:
strWhat = "Field 3, Field 4, Field 8, Field 9, Field 10"
strRep = "ID ,Type, ISIN, Date"

Unless, your ""ID ,Type, ISIN" is a part of your UDF and will ALWAYS be a part of your outcome.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mohamadmh]

Hi Andy,

Understood, apologies for not following. Yes, ""ID ,Type, ISIN" is a part of your UDF and will ALWAYS be a part of your outcome if in Cell A1 contains one of these items "Field 3, Field 4, Field 8, Field 9, Field 10

 

[Andrzejek]

So, no matter what's in cell A1, you want to place hard-coded "ID, Type, ISIN" and grab the last element from cell A1 (where there is a comma delimited text) and place this element at the and of "ID, Type, ISIN".
Is that correct?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Based on this in Excel:

With this code:

Option Explicit

Function Replace3(ByRef strExpression As String) As String
Const XYZ As String = "ID, Type, ISIN, Date, "
Dim ary() As String
Dim strOut As String

ary = Split(strExpression, ",")
strOut = Trim(ary(UBound(ary)))
Replace3 = XYZ & strOut

End Function

You get in cell B1: [tt]ID, Type, ISIN, Date, Field 10[/tt]

or...

Option Explicit

Public Function MyReplace(ByRef strIn As String) As String
Const XYZ As String = "ID, Type, ISIN, Date, "
Dim i As Integer
[green]
'Find the position of last comma[/green]
For i = Len(strIn) To 1 Step -1
    If Mid(strIn, i, 1) = "," Then
        Exit For
    End If
Next i

MyReplace = XYZ & Mid(strIn, i + 1)

End Function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mohamadmh]

Thank you Andy! It works great. Appreciate your help!

 

[combo]

If there are no issues with substrings (as 'Field 1' and 'Field 10') and duplicate entries, I would separate strings in templates with comma only and use Replace function:

[pre]strWhat = "Field 3,Field 4,Field 8,Field 9,Field 10"
strRep = "ID,Type,ISIN,Date"

arrWhat = Split(strWhat, ",")
arrRep = Split(strRep, ",")

Replace3 = expression ' <= should be here, otherwise you your loop overwrites prevous changes

For i = LBound(arrWhat) To UBound(arrWhat)
Replace3 = Replace(Replace3, arrWhat(i), arrRep(i))
Next i[/pre]

combo

 

[mohamadmh]

Thanks everyone this is very helpful