Chapter 28: Replacing Characters in a String

Overview

The code in this chapter creates a worksheet function that replaces all instances of a specified character in a string with another specified character. For example, if you had a cell in your spreadsheet containing the text ‚“***Replacement String***, ‚½ you could use the Replace formula to replace the * character with the ! character so that the string will read ‚“!!!Replacement String!!! ‚½

This is quite useful for data imported or pasted in from another application. Imported data sometimes includes extraneous characters that need to be removed altogether or changed to another character.

Because this is a public function, it can also be used inside your own VBA code. Insert the following code into a module:

 Function REP(target As String, r As String, s As String) 
 temp = "" 
 For n = 1 To Len(target) 
 
 If Mid(target, n, 1) = r Then 
 
 temp = temp & s 
 
 Else 
 
 temp = temp & Mid(target, n, 1) 
 End If 
 REP = temp 
 Next n 
 End Function 

This is a function, and it behaves differently than a subroutine in that it is called from the spreadsheet itself by entering a formula, which returns a result to the spreadsheet cell it is in. Three parameters are passed to it:

Name	Type	Description
target	String	String to be searched or a cell reference to it
r	String	Character to be searched for
s	String	Character to replace searched character

A variable temp is set to null. This will be used to build the new string. A For..Next loop is then used to move through target one character at a time. The Mid function breaks out each character from the string and tests it to see if it equals the search character. Note that it has to be an exact match because the match is case sensitive.

If there is a match, the character represented by s is concatenated onto the end of temp . If there is no match, the original character is concatenated onto temp . Finally, the variable REP , which represents the function, is given the string value of temp , and this places it back into the cell.

You do not need to run the code to try this ‚ just enter a formula as you normally would within a cell of the spreadsheet:

 =REP(A1,"*","!") 

If you click the Formula Paste icon on the Formula toolbar, this formula will be under the User Defined Formula section, and you can use it as you would any other formula. If you do not put in the proper parameters, you will get the standard Excel errors. Your worksheet should look like Figure 28-1.

Figure 28-1: Example of replacement function

Tip ‚  

A handy way to remove a particular character from a string is to set the search string as normal and then set the replacement string as an empty string:

 =REP(A1,"*","")