String manipulation is a crucial skill in VBA programming. The skill level of a VBA developer is often determined by how well he/she can manipulate string data. Excel is very strong in mathematics operations and comes with loads of built-in calculation functions. But text manipulation is less straightforward and always requires some creativity and experience.

In this article, I am going to show you how to remove characters from strings. We’ll go through a series of common scenarios, and you’ll learn about how to tackle them with the Replace, Left, Right, Trim, and Instr VBA functions.

Remove All Occurrences of Specific Characters from Strings

The basic VBA skill to remove characters from a string is with the Replace method.

The basic  syntax of VBA Replace method you need to remember is:

Replace (inputString, findwhat, replace)

Example 1 is the most typical approach where the Replace method is used to remove all occurrences of specific character(s) from the input string.

Example 1 – The Most Common Approach, Case Sensitive

Here we want to remove all occurrence of “b” from variable input1,”aabbccAABBCC”, with the expected output of “aaccAABBCC”. In line 6 of the macro, the Replace method looks for “b” and replaces it with an empty string “”.

Sub removechar()
Dim input1 As String
Dim result As String
input1 = "aabbccAABBCC"
'to remove all occurrences of b from input string
result = Replace(input1, "b", "")
MsgBox result
End Sub

After running the macro, the answer is displayed in the message box:

However, this approach with the Replace method is case sensitive. Only the lower case “b” characters were removed but not the “B”. We can modify our macro to automatically handle both lower and upper cases.

Example 2 – Simple Non-Case Sensitive Approach

We can enhance the macro to remove both the lower case “b” and upper case “B” from the input string. In macro removechar2 below, the character to be remove is defined in line 6. Then, in line 7, we first remove the lower case, and then in line 8 the upper case is also removed.

Sub removechar2()
Dim input1 As String
Dim remove1 As String
Dim result As String
input1 = "aabbccaabbcc"
remove1 = "b"	'this is the char to be removed
result = Replace(input1, LCase(remove1), "")
result = Replace(result, UCase(remove1), "")
MsgBox result
End Sub

Remove the First n Occurrences of Specific Characters from Strings

Sometimes we want to remove only the first n occurrences of specific characters. This is easy to do by using the optional argument “count” of the VBA Replace method:

Syntax:

Replace (inputString, findwhat, replace, start_position, count)

The following macro “removechar3” removes the first 3 occurrences of “b” from input1 “aabbccaabbcc”. The expected result is “aaccaabcc”.

Sub removechar3()
Dim input1 As String
Dim remove1 As String
Dim result As String
input1 = "aabbccaabbcc"
remove1 = "b"
'remove the first 3 occurrences of "b" from input string
result = Replace(input1, remove1, "", , 3)
MsgBox result
End Sub

After running the macro, the answer is displayed in the message box:

Remove Characters from Left of Strings

Here we have a list of staff IDs in the format “X12345678” with 9 characters each. We want to remove the leading letter from every ID in the list.

We can use the Right function for this purpose. While each staff ID is 9 characters long, removing 1 character from the left is equivalent to outputting 8 (9 minus 1) characters from the right.

Sub removeLeft()
Dim cell As Range
Dim MyRange As Range
Dim tmp As String
Set MyRange = Selection  'this is your range of data
'loop through every cell in range and remove 1 char from left
For Each cell In MyRange.Cells
    tmp = cell.Value
    'output n - 1 characters from the right
    cell.Value = Right(tmp, Len(tmp) - 1)
Next
End Sub

However, after running the above macro, we realize that it did not fully meet our expectations (see the picture below). Because some of the original IDs have numeric parts with leading zeros. (Lines 1, 5 and 7), Excel is too clever and automatically trims those zeros. (e.g. 021 is treated by Excel as 21.) We want to make sure the zeros remain after the leading letters were removed.

We can achieve this by forcing Excel to treat numeric data as strings by adding a leading apostrophe to the result. For example, ‘021 will be treated by Excel as string. To achieve this, we can modify line 9 of the macro as shown below:

Sub removeLeft()
Dim cell As Range
Dim MyRange As Range
Dim tmp As String
Set MyRange = Selection  'this is your range of data
'loop through every cell in range and remove 1 char from left
For Each cell In MyRange.Cells
    tmp = cell.Value
    'output n-1 char from the right, and add apostrophe
    cell.Value = "'" & Right(tmp, Len(tmp) - 1)
Next
End Sub

Remove Characters from Right of Strings

Example 1 – Remove a Fixed Number of Characters from the Right

To remove a fixed number of characters x from the right of strings, you can use the LEFT function. The macro is almost the same as the macro above for removing characters from the left.

Sub removeRight()
Dim cell As Range
Dim MyRange As Range
Dim tmp As String
Set MyRange = Selection  'this is your range of data
'loop through every cell in range and remove 1 char from right
For Each cell In MyRange.Cells
    tmp = cell.Value
    'output n - 1 characters from the left
    cell.Value = Left(tmp, Len(tmp)-3)
Next
End Sub

Example 2 – Remove a Variable Number of Characters from the Right

If we want to remove characters from the right based on the variable position of a certain character(s), we can use the INSTR function in conjunction with the LEFT function.

Here we have a list of email addresses. We want to remove the domain names on the right of each address.

We can find out the position of “@” with the following VBA statement, which in the case returns 9. Therefore, to remove the domain name, we have to extract the first 8 characters.

x = Instr("[email protected]","@")

The following macro loops through every cell in a selected range and removes the domain names.

Sub removeDomain()
Dim cell As Range
Dim MyRange As Range
Dim tmp As String

Set MyRange = Selection  'this is your range of data

'loop through every cell in range and remove characters after @
For Each cell In MyRange.Cells
    tmp = cell.Value
    '
    cell.Value = Left(tmp, InStr(tmp, "@") - 1)
Next
End Sub

Here’s a couple key takeaways to remember about removing characters from one side or the other of strings:

Scenario	Function to be used
Remove characters from the Left	RIGHT
Remove characters from the Right	LEFT
Position specific remove	INSTR (+ LEFT/RIGHT)

Removing Unwanted Spaces from Strings

There are two common scenarios where we want to remove unwanted spaces from strings:

• Strings with leading and trailing spaces
• Unwanted extra spaces within string (e.g. double spaces)

Remove Leading and Trailing Spaces from Strings

We can use the Trim function to remove leading and trailing spaces. In the example below we have an input string of  "   This is my data   " (line 5). The Trim function is used in line 6. The macro produces a message box to show a comparison of the input and output.

Sub removespace1()
Dim MyInput As String
Dim result As String
'here is the input string with leading and trailing spaces
MyInput = "   This is my data   "  
result = Trim(MyInput)  'remove leading and trailing spaces

'display result in msgbox
MsgBox "Original text: >" & MyInput & "<" & Chr(10) & _
    "Original length: " & Len(MyInput) & Chr(10) & _
    "Final text: >" & result & "<" & Chr(10) & _
    "Final length: " & Len(result)
End Sub

Remove All Extra Spaces from String

Removing “extra spaces” is different from removing “all spaces.” You can visualize the difference here:

The macro removeAllUnwantedSpace below removes leading and trailing spaces as well as all unwanted repeated spaces. Pay special attention to line 10 to 12. The same statement must be run 3 times to ensure all repeated spaces are replaced with single spaces.

Sub removeAllUnwantedSpace()
Dim MyInput As String
Dim result As String
'here is the input string with unwanted spaces
MyInput = "   This     is    my   data   "
'first remove leading and trailing spaces
result = Trim(MyInput)  
'then replace double spaces with single space
'this step has to be repeated 3 times
result = Replace(result, "  ", " ")
result = Replace(result, "  ", " ")
result = Replace(result, "  ", " ")
'display result in msgbox
MsgBox "Original text: >" & MyInput & "<" & Chr(10) & _
    "Original length: " & Len(MyInput) & Chr(10) & _
    "Final text: >" & result & "<" & Chr(10) & _
    "Final length: " & Len(result)
End Sub

Remove Numbers from String

Sometimes we want to remove all numeric characters from strings. We can achieve this with a For-Next loop to remove each of the digits from 0 to 9.

The custom VBA function below shows how to remove all numeric characters from an input string.

Function removenumbers(ByVal input1 As String) As String
Dim x
Dim tmp As String
tmp = input1
'remove numbers from 0 to 9 from input string
For x = 0 To 9
    tmp = Replace(tmp, x, "")
Next
'return the result string
removenumbers = tmp
End Function

You could also consider using a regular expression for a task like this.

Remove line breaks from string

Sometimes our data may contain line breaks with the strings and we want to remove them. A line break is actually an invisible character. We can simply use the VBA Replace function to remove them.

There are 2 types of line break characters. The most common one is Chr(10). But a less common one is Chr(13). Chr(13) is more common on the Mac. We can use a statement like this to remove such line break characters:

result = Replace(myString, Chr(10))
'or
result = Replace(Replace(myString, Chr(10)), Chr(13))

Remove Accented Characters from Names

There is a special situation that we want to remove the “accent” from accented characters in names. As an example, we have a list of French names below. In this case, we want to stick to “regular” English letters. Our expected result is shown on the right.

The macro “removeAccented” below loops through every cell in a selected range and replaces all accented characters with the corresponding regular English letters.

At the beginning of the macro, two constant strings were defined: “Accent” holds all accented characters; and “Normal” holds the corresponding regular English letters.

The For-Next loop (which begins in line 13 of code) loops through every accented character in the constant “Accent” and replaces with the alphabet in the same position in the constant “Normal”, e.g. “à” which is in position 1 of “Accent” will be replaced with “a” in position 1 of “Normal”.

Sub removeAccented()
Const Accent = _
"àáâãäåçèéêëìíîïðñòóôõöùúûüýÿŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝ"
Const Normal = _
"aaaaaaceeeeiiiidnooooouuuuyySZszYAAAAAACEEEEIIIIDNOOOOOUUUUY"
Dim cell As Range
Dim x As Integer
Dim tmp As String
'loop through each cell in selection
For Each cell In Selection.Cells
    tmp = cell.Value
    'loop through the constants, and replace 
    For x = 1 To Len(Accent)
        tmp = Replace(tmp, Mid(Accent, x, 1), Mid(Normal, x, 1))
    Next
    cell.Value = tmp
Next
End Sub

Conclusion

You can see in the above examples that text manipulation in Excel VBA indeed requires some creativity. There is no single universal approach to remove characters from strings which can serve all scenarios. The Replace function is the most important function to use. In some cases, you also have to apply the Left, Right, Trim, Instr functions.

The technique to loop through cells in a range has also been used in most of the examples above. You may refer to my article on this topic for more examples on the topic.