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.
Formula solution.
Use the case-insensitive SEARCH function to locate the position, and REPLACE to update the text, with an IFERROR to handle no match.
=IFERROR(REPLACE(A2,SEARCH(“input”,A2),5,”output”),A2)
The 5 reflects the length of the find-text (“input”), so adjust accordingly.
LikeLike
Though it only replaces the first matching instance.
LikeLike
Hello MF,
what about Ctrl H and using placeholders ‘?’ and ‘*’?
Sabine
LikeLike
Hi Sabine
I am afraid that does not work.
LikeLike
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.
Sabine
LikeLike
Hi Sabine, you also changed the “ADBC” to “_”
That’s tricky though
LikeLike
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:
_D_d_D_ADBC_D
LikeLike
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.
LikeLike