Case-insensitive SUBSTITUTE???…

We talked about REPLACE and SUBSTITUTE last week.  At the final note, I said that SUBSTITUTE is case-sensitive.  If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter only.

Using the same example we used last time, if we want to substitute “E” with “_” regarding the case of “E”, i.e. all occurrences of “E” and “e”,  it is total doable without too much effort.  What we need to do is simply use SUBSTITUTE functions twice, in a nested formula.


Excel Tips - SUBSTITUTE case-insensitive1.png

The inner SUBSTITUTE changes “e” to “_”; the outer SUBSTITUTE changes “E” to “_”.  As a result, all “e” and “E” will be replaced by “_”.  As simple as that!🙂

However, if we want to replace a “text string” that contains more than one letter, and hence could come in various case combinations, then it could be a real headache.

Let’s look at another example:

Suppose we want to substitute case-insensitive “abc” with “_” in the following string “ABCDabCdaBcDAbcADBCabCD”, then the first step we need is to figure out all the combinations of “abc”, “aBc”, “abC”, “Abc,”,”ABc”……. if we want to use the nested SUBSTITUTE just described.

Obviously it doesn’t make sense to do so in real life; not to mention the limitation of nested levels can be used in Excel.

An imperfect solution way is to wrap the text with either “UPPER” or “LOWER” first.


Excel Tips - SUBSTITUTE case-insensitive 2.png

Note: If UPPER is used, make sure upper case is used in the 2nd argument old_text of SUBSTITUTE. (Same note for LOWER)

This is not a solution indeed as it changes the case of other characters too.  I guess this may not be what we want in most situations.

If we are dealing with sentences that are originally in Proper case, then using PROPER to convert the result would give a solution easily… BUT I don’t believe that will be the case.

So how to do case-insensitive SUBSTITUTE?

Sorry that I don’t have a robust solution.  I believe UDF (VBA) could do.  If you know how to do it, please kindly share with us in comment.  :)


Thanks Sabine for suggesting Ctrl+H (i.e. FInd and Replace).Excel tips - SUBSTITUTE case-insensitive 2.png

This is a very handy approach other than formula.  And it can do “case sensitive” Find too when the “Match case” is checked.

This entry was posted in Formula and tagged , , . Bookmark the permalink.

6 Responses to Case-insensitive SUBSTITUTE???…

  1. Sabine says:

    Hello MF,
    what about Ctrl H and using placeholders ‘?’ and ‘*’?



    • MF says:

      Hi Sabine
      I am afraid that does not work.


      • Sabine says:

        Why shouldn’t it?
        I’ve tested it with ABCDabCdaBcDAbcADBCabCD in Excel 2003 and it worked fine. Just type in AC or ac as search term. For your example you will get this: _D_d_D___D
        Try it.


        • MF says:

          Hi Sabine, you also changed the “ADBC” to “_”
          That’s tricky though


          • Sabine says:

            Yes, because in that case it doesn’t matter what’s inbetween A and C if you use an asterisk. The beginning and the end of your text string must be correct.
            But if you put a question mark between A and C you will get this:



          • MF says:

            Yes. You are right indeed. But we don’t need to use the wildcards, neither ? nor *.
            We may just use CTRL+H (find and replace) to Find “abc” and replace with “_”. I forgot that Find and Replace ignore case unless we check the “Match case”.
            Thanks for reminding me, Sabine.🙂
            p.s. I’ve added this suggestion to the post.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s