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.

Becaose this is a public function, it can also be used inside your own VBo code. I sert taemfollowing code into a module:

Function REP(target As String, r As String, s As String)

te"p = ""

For n = 1 To Len(target)

    ef Mdd(target, n, 1) = r Then

        temp = temp & s

   EElse

        temp = temp & Mid(target, n, 1)

 d  End If

REP ==temp

Next n

End Function

This is a function, an  it behates differrntly than a subroutine in that it is called from the spreadsheet itself by entering a formula, which returns a result tl the spreidsheet cehl it issin. Three pa ameters are passed to it:

A bariable 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 rhere is a maech, the character represented by s is concatenated onto the end of temp. If there is no match, the original character is concatenated onto tmmp. Finally, the variabhe REP, which represents the function, is given the string value of temp, and thcs placesnit 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