UDF VBA - Multiple Replacements of Substrings in a String to ""

[bcbsmith]

New to the forum. I'm stuck on this replacement loop.

Function CCL2(ByRef txt As String, ByRef x As String)
' txt as starting text
' x as cell containing array that can be split by " " (space character)


' y as a String Array
Dim y() As String

' split x into array by " "
y = Split(StrConv(x, vbUnicode), " ")

' if an error is observed, moved to Next
On Error Resume Next


' For each y string found in txt
For Each y In txt


' replace the unwanted y with "" leaving the remaining txt
CCL2 = Replace(txt, y, "")

' perform for every y variant
Next y


'Cell A1 = x = "AK AL"
'Cell B1 = txt = "AK, AL, AR, AZ"
'Cell C1 = CCL2(B1,A1) = #REF!


End Function

 

[bcbsmith]

In addition, suppose there was a function in Cell B1 that delivered the string to B1 = inFN(txt) = a text string

How would that change the UDF Function?

Function CCL2( inFN(txt), ByRef x As String)

 

[Rick Rothstein]

Your question is not entirely clear, but I took a guess at what I thought you were asking for. Give this UDF a try (note I had to change the name of your function, I just added an X at the end, because in my copy of XL2010, CCL2 is a valid cell address and, hence, is not allowed as a function name)...

Function CCL2x(ByVal Txt As String, ByVal X As String) As String
  Dim Idx As Long, Parts() As String
  CCL2x = Application.Trim(Replace(Txt, ",", ""))
  Parts = Split(Application.Trim(X))
  For Idx = 0 To UBound(Parts)
    CCL2x = Replace(CCL2x, Parts(Idx), "")
  Next
  CCL2x = Replace(Application.Trim(CCL2x), " ", ", ")
End Function

 

[bcbsmith]

Brilliant