1.8 Resize
Home and Learn - Free Excel VBA Course
4.4 Excel VBA Replace Function
The Replace function is used to replace text in a string with something else. Suppose, for example, that you have a misspelled word in cell A5. You can use Replace to change the incorrect letters with the correct ones.
You can use your spreadsheet and code from the Change Case section for this. To try it out, add two more headings in cells A4 and B4. Type the heading Original in cell A4 and the heading Replace in cell B4. Now click inside cell A5 and type the misspelled word Micrasaft. Your spreadsheet should now look like this:
To use the Replace function, you need at least three things between it round brackets:
Replace( string_to_search, string_to_replace, replace_with )
The first thing you need is a string of text to search. Next, you specify what it is you're searching for. This is the character or characters you're going to replace. The third thing you need is the new character or characters.
With the Replace function you also have an optional three things you can specify. These are:
start, count, compare
The optional parameters go after the third item in replace, with each being separated by a comma:
Replace( string_to_search, string_to_replace, replace_with, start, count, compare )
The start parameter is where in the string you want to start search from. The default is character 1, which is the first character in the string. If you want to start from a position in the string other than the first character then you need to type your start number here.
The count parameter is how many occurrences you want to replace. The default is to replace every occurrence of replace_with. If you only want to replace, say, the first two occurrences then type the number 2 here.
The compare parameter has three options: vbBinaryCompare, vbTextCompare, vbDatabaseCompare. Don't worry about compare, as it's rarely used.
As an example, add a new Sub to your coding window. Call it ReplaceExample. Add the following code for the new Sub:
Dim OriginalText As String
Dim CorrectedText As String
OriginalText = Range("A5").Value
CorrectedText = Replace(OriginalText, "a", "o")
Range("A5").Offset(, 1).Value = CorrectedText
Your coding window will then look like this
We have two String variables set up here, OriginalText and CorrectedText. The value for the OriginalText variable is coming from the Range A5 on the spreadsheet. We then have this:
CorrectedText = Replace(OriginalText, "a", "o")
So we have our Replace function on the right of the equal sign. The first item between the round brackets of Replace is the variable name OriginalText. This is the text that Replace will be searching. The next item is the character that is incorrect, the letter "a". The "a" is surrounded by double quotes. Finally, we need the new text that we want in the string, which is the letter "o". All three items are separated by commas.
The final line puts the corrected text into cell B5 on the spreadsheet.
Run your code and try it out. Your spreadsheet should change to this:
You can replace more than one character, if you need to. The following code replaces the misspelled Microsft with Microsoft:
CorrectedText = Replace(OriginalText, "sft", "soft")
You can replace spaces in text by typing two double quotes. The first set of double quotes will have a space between them while the second set has no space. For example:
CorrectedText = Replace("M i c r o s o f t ", " ", "")
This time, the word Microsoft has a space after every letter. We want to remove the space. The second parameter of the Replace function is two double quotes with a space between them. The third parameter of the Replace function is two double quotes with no space between them. Two double quotes together mean "no characters".
In the next lesson, we'll look at three more Excel VBA string functions: InStr, InStrRev, StrReverse.
Next Lesson: 4.5 In the String? >