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 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. Copy and paste this table into cell A1 in Excel 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. 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. Copy and paste this table into cell A1 in Excel With this user defined function we can easily take care of the mess with a simple looking formula without nesting multiple SUBSTITUTE functions. Where $D$2:$D$5 is a range containing the characters we want to remove (#, $, % and !).
