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