Multiple Substitute Formula

MaryAnn asks an interesting question.

In column A there is text that may or may not contain the name of a US state. In column B there is a list of US states. In column C, we want the column A text without the state names. I think this would be a pretty trivial piece of VBA, but I wanted to see if I could do it in a formula. Here’s what I came up with:


Got that? OK, maybe a little explanation is in order. As usual, I will explain from the inside out. Let’s start with this little nugget


Normally with FIND, you pass a find_text argument and a within_text argument and you get a number showing the position of find_text within within_text, like =FIND("Kus","Dick Kusleika") would return 6 because the find_text “Kus” first appears in the 6th position of within_text. If find_text is nowhere to be found in within_text, you get an error. What I’ve done above is pass an array of find_text’s and checked to see if any of the states exist in A1. The result of this is a 56 element array that kind of looks like this {TRUE;TRUE;TRUE....TRUE}. If there is no state name, all 56 elements will be TRUE. If there is a state name, at least one of the elements will be FALSE. That brings me to


MATCH returns the position of the first argument in the list (the list is the second argument). This MATCH function is looking for FALSE in the 56 element array described above. If it finds a FALSE in the list, it returns the position in the list of the first one it finds. If A1 is “Chairs Idaho”, the MATCH function will return 15 because Idaho is the 15th name in the list in column B. MATCH will return an error if there are no FALSE elements in the list, so I have to use the old IF(ISNA trick


I set the TRUE condition to 0. Since there are no state names if ISNA is true, I don’t really care what position it returns. Now that I know the position of the state in the list, I can return the state name with INDEX


I substitute and empty string for the state name


And TRIM the whole thing to remove leading and trailing spaces.

Posted in Uncategorized

16 thoughts on “Multiple Substitute Formula

  1. Dick –

    Interesting post! Thank you for sharing.

    I have two questions:

    1. For my edification (because my VBA is very limited), would you do this same task in VBA? If it’s a lot of code, feel free to disregard this question.

    2. As a whole, your formula worked perfectly. When I tried to drill down to the individual pieces (following your analysis), though, I got stuck:

    =FIND($B$1:$B$5,A1) evaluates to #VALUE in all cases

    =ISERR(FIND($B$1:$B$5,A1)) therefore always evaluates to TRUE. (Not desirable, under the circumstances.)

    Can you explain what it is about =MATCH(FALSE,ISERR(FIND($B$1:$B$5,A1)),FALSE) that makes the inner FIND function correctly handle the array?


  2. @Dick…

    It looks like if you move your entire list down one row and leave A1 empty, then this slightly shorter formula appears to work…


  3. For fun, here’s what I arrived at (array-entered):


    Of course the big caveat is that there is no more than 1 U.S. state/territory per cell in col. A.

  4. Dick,
    Thank you for the nice post
    To work all formula must be matrix validated (CTRL+Uppercase+Enter)
    Best regards
    Jean Paul

  5. Michael

    The following User Defined Function will produce the same results

    (Fingers crossed this comment box presents it correctly)

    Function RemoveState(StatesRange As Range, TargetString As String) As String
        Dim rng As Range, str As String

        str = TargetString
        For Each rng In StatesRange
            If InStr(1, str, rng.Value) <> 0 Then
                str = Trim(Replace(str, rng.Value, “”))
                Exit For
            End If
        RemoveState = str
    End Function

  6. Nice work, Dick!

    One question: What will the result be if the string contains two states like “Chairs Florida New York”?

  7. @Jean Paul… The formula I posted does **not** require you to commit it using Ctrl+Shift+Enter… the formula works fine just using Enter by itself to commit it.

  8. Wasn’t this covered way back in David Hager’s Excel newsletter?

    Make the ‘state’ list include D.C. as well as a blank row below Wyoming, so 52 rows in all. Then enter the following array formula in C1.


    Augmenting lists usually eliminates the need for space- and recalc time-wasting redundant expressions.

  9. @fzz… Yes, that seems to work although you need to encase it within a TRIM function call to get rid of possible multiple internal and/or trailing spaces.

  10. Micheal, Jean Paul: Mine are array formulas, entered with ctl+shift+enter. See

    When I was writing this post, I was going to point to that post for anyone who doesn’t know what an array formula is. When I got there, I learned that my new CodeColerer add-in was screwing up code tags with no arguments (or so I thought) and that post was a mess. By the time I fixed it all up (with JP’s help), I’d forgot about creating the link.

  11. Yeah, I forgot the TRIM.

    Another alternative, if one had generalized concatenation and regular expression substitute functions, would be to define a name (REGEX) referring to the formula =”(“&MID(generalized_concatenation(“| “&B1:B51&” “),2,1024)&”)”, then use simpler formulas like =TRIM(regular_expression_substitute(A1,REGEX,” “)).

    Another wrinkle: probably only want to remove state names as words rather than as substrings, e.g., probably don’t want to remove Georgia from ‘Georgian Armoire’.

  12. Hmmm, I tried the internal components both as standard formulas and CSEs. If I find the time I’ll check it out again. Maybe Excel was just being weird…

  13. Hello,
    How would I do if for the RemoveState function I wanted to have a list of regex and not a list of string ?
    ( Function RemoveState(StatesRange As Range, TargetString As String) As String
    Dim rng As Range, str As String

    str = TargetString
    For Each rng In StatesRange
    If InStr(1, str, rng.Value) 0 Then
    str = Trim(Replace(str, rng.Value, “”))
    Exit For
    End If
    RemoveState = str
    End Function )

Leave a Reply

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