Multiple Substitute UDF

Have you ever written this formula?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"AND","")," INC","")," LLC","")," LTD","")," DBA","")," ",""),".",""),",",""),"&",""),"-",""),"/",""),"'","")

I just did. It gets the job done, but it stinks. Here’s its replacement.

=subst(UPPER(A3),""," AND "," INC"," LLC"," LTD"," DBA"," CO"," ",".",",","&","-","/","'")

That’s a little better (assuming it works). On a side note, I wish Excel had built-in constants for formulas, so the above formula would look like this.

=subst(UPPER(A3),xlNULLSTRING," AND "," INC"," LLC"," LTD"," DBA"," CO",xlSPACE,".",xlCOMMA,"&","-","/",xlSINGLEQ)

Maybe I’ll create a Sheet template with those names defined. Or is that better in a Book template? Anyway, here’s the code for the poorly named Subst function.

The ParamArray argument takes as many arguments as you want to throw at it. For some reason, I couldn’t pass OldText by reference to the sorting procedure, so I had to copy it to another variable first. I sort the terms by length so that “corporation” gets replace before “corp”. Otherwise, I’ll be left with “oration”, which is just silly.

Once sorted, I simply replace all of the old with the one new, and return the string. It worked well for the one application I’ve used it for and it was a heck of a lot easier to update. Thoughts?

5 thoughts on “Multiple Substitute UDF

  1. Huh, I’m working on a project right now that this could make much easier.

    I’ll let you know how it works out.

    And, thanks! This is going to make my formulas much more powerful.

  2. One of my conventes is to start a UDF with F_.
    That makes them very easily detectable and disernable in the list of formulae; any interference with reserved terms is excluded as well.

    I’m not a fan of quotation marks, so if the items that have to be replaced will be stored in a string separated by pipelines it’s more to my liking.
    I found a ‘simpler’ method to replace in descending order of itemlength.

    Function F_Subst(c00 As String, c01 As String, c02 As String) As String
    F_Subst = c00

    sn = Split(c02, "|")
    sp = Evaluate("index(len({""" & Replace(c02, "|", """,""") & """}),)")

    For j = 0 To UBound(sn)
    F_Subst = Replace(F_Subst, sn(Application.Match(Application.Max(sp), sp, 0) - 1), c01, , 1)
    sp(Application.Match(Application.Max(sp), sp, 0)) = 0
    Next
    End Function

    in a cell:

    =F_subst("this is the text to replace","","is|ext |o repl")

  3. Hi all,
    Here’s an other approach using Regular Expression :

    Public Function F_Subst(text As String, NewText As String, ParamArray OldText() As Variant) As String

    Dim sReturn As String
    Dim vArray As Variant
    Dim sPattern As String
    Dim i As Integer

    Const RegExSpecialChar = "[\^$.|?*+(){}"

    sReturn = text
    vArray = OldText

    For i = LBound(vArray) To UBound(vArray)
    If InStr(1, RegExSpecialChar, vArray(i)) > 0 Then vArray(i) = "\" & vArray(i)
    Next i

    sPattern = "(" & Join(vArray, "|") & ")*"
    With CreateObject("VBScript.Regexp")
    .Pattern = sPattern
    .Global = True
    .Ignorecase = True 'optional
    sReturn = .Replace(sReturn, NewText)
    End With

    F_Subst = sReturn

    End Function

  4. I’ve had one of these in my library for a while now. Mine’s slightly different in application, though: it allows many-to-1 substitutions as well as 1-to-1 substitutions. The 1-to-1 version is the same as iterating multiple times. Here’s the code (hopefully this formats correctly…):

    Yeesh, I didn’t realize until I started copy/pasting how subdivided my code is. Anyway, GoodReplace is easier to call from within VBA, but it can be called as a UDF as well: =goodreplace(“asdfg”,{“a”,”s”,”d”,”f”,”g”},{“B”,”r”,”y”,”a”,”n”})

Leave a Reply

Your email address will not be published. Required fields are marked *