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

bcbsmith

New Member
Joined
Apr 18, 2016
Messages
3
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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)


 
Upvote 0
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)...
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top