In this post we’re going to explore how to find and remove multiple text strings from within another string.

Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text.

Copy and paste this table into cell A1 in Excel

Text Revised Text
Ge%t rid% of thes%e perce%nt sig%ns
=SUBSTITUTE(A2,"%","")

 

In the above example we can use the SUBSTITUTE function to clean the text of all the percentage signs by replacing them with an empty string (“”) in the following formula.

=SUBSTITUTE(A2,"%","")

 

Copy and paste this table into cell A1 in Excel

Text Revised Text
G$$e%t ri$d$% of thes$$$%e pe$rce%nt and dol$lar sig%ns
=SUBSTITUTE(SUBSTITUTE(A2,"$",""),"%","")

 

Now if we had add in another rogue character we wanted to get rid of ($) we could get rid of both characters by using a nested SUBSTITUTE formula.

=SUBSTITUTE(SUBSTITUTE(A2,"$",""),"%","")

 

As we add more and more items we want to remove we need to nest more and more SUBSTITUTE functions and this will become unmanageable. So instead we will create a user defined function in VBA to simplify this.


Function REMOVETEXTS(strInput As String, rngFind As Range) As String
    
Dim strTemp As String
Dim strFind As String

strTemp = strInput

For Each cell In rngFind
    strFind = cell.Value
    strTemp = Replace(strTemp, strFind, "")
Next cell
    
REMOVETEXTS = strTemp
    
End Function

Copy and paste this table into cell A1 in Excel

Text Revised Text
T%%his$$ te$xt go%t
=REMOVETEXTS(A2,$D$2:$D$5)
m#esse#d u#p s%ome!!!how
=REMOVETEXTS(A3,$D$2:$D$5)
N#$%o!t sure !$h$%ow $%
=REMOVETEXTS(A4,$D$2:$D$5)
but I !nee%d $to fix it
=REMOVETEXTS(A5,$D$2:$D$5)

With this user defined function we can easily take care of the mess with a simple looking formula without nesting multiple SUBSTITUTE functions.

=REMOVETEXTS(A2,$D$2:$D$5)

Where $D$2:$D$5 is a range containing the characters we want to remove (#, $, % and !).

How-To-Find-And-Remove-Multiple-Text-Strings-Within-A-Text-String How To Find And Remove Multiple Text Strings Within A Text String