12 VBA Codes to Remove Characters From String in Excel

In Excel, sometimes we need to remove certain characters from a string, which can be a tedious task when dealing with large amounts of data. However, VBA (Visual Basic for Applications) provides a simple and efficient solution to this problem. With just a few lines of code, you can easily remove unwanted characters from a string in Excel. In this article, we will explore how to use VBA to remove characters from a string in Excel.

VBA to Remove Characters from String in Excel.xlsm

Table of Contents

How to Use These VBA Codes in Excel?

To run these VBA codes, follow the steps below:

Press ALT + F11 to open the Visual Basic Editor.

Go to Insert > Module.
Copy the VBA code and paste it on the newly created module.
Run the VBA code by pressing the F5 key.
Or, you can switch back to your Excel window and press ALT + F8 to open the Macro dialog box. From there choose the macro name and hit the Run button.

Once you run the VBA code, you will be asked to select a range with a prompt. Just input the range and necessary inputs, and it will remove characters from the string straightaway.

Delete a Specific Number of Characters From the Left of a String Using VBA

This code prompts you to select a range of cells. Then, it prompts you to enter the number of characters to remove from the left of each string. It then loops through each cell in the selected range and applies the Right function to remove the specified number of characters from the left of each string.

Sub RemoveCharsFromLeft()

Dim selectedRange As Range
Dim numCharsToRemove As Integer
Dim cell As Range

' Prompt user to select a range
Set selectedRange = Application.InputBox(prompt:="Select a range to remove characters from the left of strings", Type:=8)

' Prompt user to enter the number of characters to remove
numCharsToRemove = InputBox("Enter the number of characters to remove from the left of each string:")

' Loop through each cell in the selected range
For Each cell In selectedRange
' Remove the specified number of characters from the left of the cell's value
cell.Value = Right(cell.Value, Len(cell.Value) - numCharsToRemove)
Next cell

End Sub

Delete a Specific Number of Characters From the Right of a String Using VBA

This code prompts you to select a range of cells. Then it prompts you to enter the number of characters to remove from the right of each string. It then loops through each cell in the selected range and applies the Left function to remove the specified number of characters from the right of each string.

Sub RemoveCharsFromRight()

Dim selectedRange As Range
Dim numCharsToRemove As Integer
Dim cell As Range

' Prompt user to select a range
Set selectedRange = Application.InputBox(prompt:="Select a range to remove characters from the right of strings", Type:=8)

' Prompt user to enter the number of characters to remove
numCharsToRemove = InputBox("Enter the number of characters to remove from the right of each string:")

' Loop through each cell in the selected range
For Each cell In selectedRange
' Remove the specified number of characters from the right of the cell's value
cell.Value = Left(cell.Value, Len(cell.Value) - numCharsToRemove)
Next cell

End Sub

VBA to Remove All Characters Before a Specific Character From a String

This VBA code prompts the user to select a range of cells in Excel and input a character. It then iterates through each cell in the selected range, utilizing the InStr function to find the position of the specified character within each cell’s content. If the character is found, the code employs the Mid function to extract and retain all characters to the right of the specified character, essentially removing everything to the left of it.

Sub RemoveAllBeforeASpecificChar()

Dim rng As Range
Dim removeChar As String
Dim cell As Range
Dim i As Long

'Prompt user to select a range
Set rng = Application.InputBox(prompt:="Select the range you want to remove characters from the left", Type:=8)

'Prompt user to input the character before which they want to remove left characters
removeChar = InputBox("Enter the character before which you want to remove left characters")

'Loop through each cell in the selected range
For Each cell In rng.Cells
'Find the position of the removeChar in the cell's value
i = InStr(cell.Value, removeChar)

'If the removeChar is found in the cell's value, remove all characters to the left of it
If i > 0 Then
cell.Value = Mid(cell.Value, i + 1)
End If
Next cell

End Sub

Remove Non-Printable Characters in Excel [5+ Methods]

VBA to Remove All Characters After a Specific Character From a String

This VBA code initiates by prompting the user to select a range of cells in Excel and input a character. Subsequently, it instructs the user to specify the character after which they want to remove all subsequent characters from each string. The code then iterates through the selected cell range, utilizing the InStrRev function to identify the position of the specified character, and subsequently uses the Left function to extract all characters to the left of it, effectively removing everything to the right of the specified character.

Sub RemoveAllAfterASpecificChar()

Dim rng As Range
Dim removeChar As String
Dim cell As Range
Dim i As Long

'Prompt user to select a range
Set rng = Application.InputBox(prompt:="Select the range you want to remove characters from the right", Type:=8)

'Prompt user to input the character after which they want to remove the right characters
removeChar = InputBox("Enter the character after which you want to remove right characters")

'Loop through each cell in the selected range
For Each cell In rng.Cells
'Find the position of the removeChar in the cell's value
i = InStrRev(cell.Value, removeChar)

'If the removeChar is found in the cell's value, remove all characters to the right of it
If i > 0 Then
cell.Value = Left(cell.Value, i - 1)
End If
Next cell

End Sub

Clear All Characters Except Letters From a String Using VBA

This VBA code is designed to remove all special characters, excluding letters (A-Z or a-z) and spaces, from cells within a user-selected range in Excel. Upon selecting a range, the code iterates through each cell and examines each character in the cell’s value. For each character identified as a letter or space, it compiles a new string, excluding non-letter and non-space characters. Ultimately, the cell’s original value is replaced with the cleaned string, effectively removing all special characters.

Sub RemoveAnyCharsExceptLetters()

Dim selectedRange As Range
Dim cell As Range
Dim newStr As String
Dim i As Integer

' Prompt user to select a range
Set selectedRange = Application.InputBox(prompt:="Select a range to remove special characters", Type:=8)

' Loop through each cell in the selected range
For Each cell In selectedRange
newStr = ""

' Loop through each character in the cell's value
For i = 1 To Len(cell.Value)
If Mid(cell.Value, i, 1) Like "[A-Za-z ]" Then ' Matches letters and spaces
newStr = newStr & Mid(cell.Value, i, 1)
End If
Next i

' Replace the cell's value with the cleaned string
cell.Value = newStr
Next cell

End Sub

Clear All Occurrences of Any Specific Character From a String Using VBA

This code removes all occurrences of a specified character from each string in a user-selected range. The macro prompts you to select a range, and then it tells you to enter a character to remove from each string. Finally, it asks you to choose whether the operation should be case-sensitive or not.

Sub RemoveSpecificChar()

Dim selectedRange As Range
Dim charToRemove As String
Dim cell As Range
Dim caseSensitive As Boolean

' Prompt user to select a range
Set selectedRange = Application.InputBox(prompt:="Select a range to remove a character from", Type:=8)

' Prompt user to enter a character to remove
charToRemove = InputBox("Enter a character to remove from the selected range:")

' Prompt user to choose case-sensitive or non-case-sensitive operation
Select Case MsgBox("Do you want the operation to be case-sensitive?", vbYesNoCancel + vbQuestion)
Case vbYes
caseSensitive = True
Case vbNo
caseSensitive = False
Case Else
Exit Sub
End Select

' Loop through each cell in the selected range
For Each cell In selectedRange
' Remove all occurrences of the specified character from the cell's value
If caseSensitive Then
cell.Value = Replace(cell.Value, charToRemove, "")
Else
cell.Value = Replace(cell.Value, charToRemove, "", , , vbTextCompare)
End If
Next cell

End Sub

VBA to Delete First n Occurrences of Any Specific Character From a String

This code removes a specific number of occurrences of a specific character from each string in a user-selected range. The macro prompts you to select a range, and then tells you to enter a character to remove and the number of occurrences to remove. Finally, it lets you choose whether the operation should be case-sensitive or not.

Sub RemoveSpecificChars()

Dim inputRange As Range
Dim inputCells As Variant
Dim removeChar As String
Dim removeCount As Integer
Dim isCaseSensitive As Boolean

' Prompt user to select a range
On Error Resume Next
Set inputRange = Application.InputBox("Select a range:", Type:=8)
On Error GoTo 0

' Check if the input range was selected
If inputRange Is Nothing Then
MsgBox "No range was selected.", vbExclamation
Exit Sub
End If

' Prompt user for character to remove
removeChar = InputBox("Enter the character(s) to remove:", "Remove Specific Characters", "")

' Prompt user for the number of occurrences to remove
removeCount = InputBox("Specify the number of characters to remove:", "Remove Specific Characters", 1)

' Prompt user for case sensitivity option
isCaseSensitive = MsgBox("Do you want to perform a case-sensitive operation?", vbYesNo + vbQuestion) = vbYes

' Loop through each cell in input range and remove specific characters
For Each inputCells In inputRange
If Not IsEmpty(inputCells) Then
If isCaseSensitive Then
inputCells.Value = Replace(inputCells.Value, removeChar, "", , removeCount)
Else
inputCells.Value = Replace(LCase(inputCells.Value), LCase(removeChar), "", , removeCount, vbTextCompare)
End If
End If
Next inputCells

' Inform user that operation is complete
MsgBox "Specific character(s) removed from selected range.", vbInformation

End Sub

Remove Leading and Trailing Spaces Only From a String with VBA

This code removes only the leading and trailing spaces from each string in a user-selected range. It first prompts you to select a range, and then loops through each cell in the selected range. It then uses the VBA Trim function to remove any leading or trailing spaces from each string.

Sub RemoveLeadingTrailingSpaces()

Dim rng As Range
Dim cell As Range

'Prompt user to select a range
Set rng = Application.InputBox(prompt:="Select a range: ", Type:=8)

'Loop through each cell in the selected range
For Each cell In rng.Cells
'Remove leading/trailing spaces from the cell's value
cell.Value = Trim(cell.Value)
Next cell

End Sub

Clear All Extra Spaces From a String in Excel with VBA

This code removes all leading, trailing, and extra spaces between words from a selected range of cells. It first prompts you to select the range of cells to remove spaces from. For each cell in the range, it removes leading and trailing spaces using the Trim function and then removes any extra spaces between words using a loop that replaces all instances of two consecutive spaces with a single space.

Sub RemoveSpaces()

Dim rng As Range
Dim cell As Range

'Prompt the user to select the range of cells to remove spaces from
On Error Resume Next
Set rng = Application.InputBox("Please select the range of cells to remove spaces from:", "Select Range", Type:=8)
On Error GoTo 0

'Check if the user canceled the selection
If rng Is Nothing Then
MsgBox "No range was selected.", vbInformation
Exit Sub
End If

'Loop through each cell in the range
For Each cell In rng

'Remove leading and trailing spaces from the cell value
cell.Value = Trim(cell.Value)

'Remove any extra spaces between words
Do While InStr(cell.Value, "  ") > 0
cell.Value = Replace(cell.Value, "  ", " ")
Loop

Next cell

End Sub

Delete Any Kind of Line Breaks From a String in Excel with VBA

This VBA code removes all line breaks from the strings in a selected range. The code prompts you to select a range and then loops through each cell in the range. For each cell, it replaces any carriage return (vbCr), line feed (vbLf), or combination of the two (vbCrLf) with an empty string. This effectively removes any line breaks and combines multiple lines of text into a single line within each cell.

Sub RemoveLineBreaksFromString()

Dim rng As Range
Dim cell As Range

'Prompt user to select a range
Set rng = Application.InputBox(prompt:="Select a range: ", Type:=8)

'Loop through each cell in the selected range
For Each cell In rng.Cells
'Replace all line breaks with an empty string
cell.Value = Replace(Replace(Replace(cell.Value, vbCr, ""), vbLf, ""), vbCrLf, "")
Next cell

End Sub

Remove Accented Characters And Replace with Regular Characters On a String with VBA

This VBA macro lets you select a range of cells and removes any accented characters (such as é, ö, ñ, etc.) from the strings in each cell. It replaces the accented characters with their unaccented counterparts.

The macro uses two constants to map each accented character to its unaccented equivalent. If a character is a letter or a space, it is kept in the output string. If it is an accented character, it is replaced with its unaccented equivalent. Once all characters in the cell have been processed, the macro replaces the cell’s original value with the cleaned string.

Sub RemoveAccentedCharacters()

Dim selectedRange As Range

On Error Resume Next
Set selectedRange = Application.InputBox("Please select the range to remove accented characters from:", "Select Range", Type:=8)
On Error GoTo 0

' Check if the user selected a range
If selectedRange Is Nothing Then
MsgBox "No range selected. Please try again.", vbExclamation, "No Range Selected"
Exit Sub
End If

Const accentChars = "àáâãäåçèéêëìíîïðñòóôõöùúûüýÿŠŽšžŸÀÁ ÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝ"
Const normalChars = "aaaaaaceeeeiiiidnooooouuuuyySZszYAAAAAACEEEEIIIIDNOOOOOUUUUY"

Dim currentCell As Range
Dim accentIndex As Integer
Dim tmp As String

' Loop through each cell in the selected range
For Each currentCell In selectedRange
tmp = ""
' Loop through each character in the cell's value
For i = 1 To Len(currentCell.Value)
If Mid(currentCell.Value, i, 1) Like "[A-Za-z ]" Then ' Matches letters and spaces
tmp = tmp & Mid(currentCell.Value, i, 1)
Else
' Loop through accent and normal character pairs, and replace
For accentIndex = 1 To Len(accentChars)
If Mid(currentCell.Value, i, 1) = Mid(accentChars, accentIndex, 1) Then
tmp = tmp & Mid(normalChars, accentIndex, 1)
Exit For ' Exit inner loop once character is replaced
End If
Next accentIndex
End If
Next i
currentCell.Value = tmp ' Replace the cell's value with the cleaned string
Next currentCell

' Notify user that the operation is complete
MsgBox "Accented characters have been removed from the selected range.", vbInformation, "Operation Complete"

End Sub

Remove Only Numbers From a String in Excel Using VBA

This VBA macro removes all numeric characters from the selected range of cells in an Excel worksheet. It prompts you to select a range, then loops through each cell in the range and removes any characters that are numbers.

Sub RemoveNumbersFromString()

Dim rng As Range
Dim cell As Range
Dim i As Integer
Dim result As String

'Prompt user to select a range
Set rng = Application.InputBox(prompt:="Select the range you want to remove numbers from", Type:=8)

'Loop through each cell in the selected range
For Each cell In rng.Cells
'Loop through each character in the cell's value
For i = 1 To Len(cell.Value)
'If the character is not a number, append it to the result string
If Not IsNumeric(Mid(cell.Value, i, 1)) Then
result = result & Mid(cell.Value, i, 1)
End If
Next i
'Replace the cell's value with the result string
cell.Value = result
'Reset the result string for the next cell
result = ""
Next cell

End Sub

Conclusion

Removing characters from a string is a common task that can be time-consuming when done manually, but with VBA, it can be accomplished quickly and efficiently. I hope any of your data cleaning problems will fall under any of these cases. So I really hope this article will meet all of your needs. However, if you face any problems, please let me know in the comment section below.

Frequently Asked Questions

How do I delete part of text in VBA?

In VBA (Visual Basic for Applications), you can delete part of a text string using the following code:

Sub DeleteText()
Dim originalText As String
Dim newText As String

' Replace "A1" with the cell reference containing your text
originalText = Range("A1").Value

' Replace "startPosition" with the starting position of the text you want to delete
' Replace "numCharsToDelete" with the number of characters you want to delete
newText = Mid(originalText, 1, startPosition - 1) & Mid(originalText, startPosition + numCharsToDelete)

' Replace "A2" with the cell reference where you want to output the modified text
Range("A2").Value = newText
End Sub

Adjust the values of startPosition and numCharsToDelete based on your specific requirements. This VBA code deletes a specified portion of text from a given cell and outputs the modified text to another cell.

How do I trim text in VBA?

To trim text in VBA (Visual Basic for Applications), you can use the VBA TRIM function. Here’s a simple example:

Sub TrimText()
Dim originalText As String
Dim trimmedText As String

' Replace "A1" with the cell reference containing your text
originalText = Range("A1").Value

' Trim the text
trimmedText = Trim(originalText)

' Replace "A2" with the cell reference where you want to output the trimmed text
Range("A2").Value = trimmedText
End Sub

This VBA code trims leading and trailing spaces from a text string in a specified cell and outputs the trimmed text to another cell. Adjust the cell references as needed for your specific case.

Related Articles

3 Ways to Remove Specific Characters in Excel

4 Ways to Remove First Word in Excel

5/5 - (2 votes)