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.

=SUBSTITUTE(SUBSTITUTE(A1,"e","_"),"E","_")

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.

=SUBSTITUTE(UPPER(A1),"ABC","_")

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.  🙂

EDIT:

Thanks Sabine for suggesting Ctrl+H (i.e. FInd and Replace).

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