In this comprehensive Excel tutorial, you’ll learn how to remove characters in Excel in various manual, semi-automatic, and automatic methods.

You might need to delete certain characters in your Excel dataset either following a pattern or randomly. Such exercises are often necessary to clean raw data, correct text errors, improve workbook performance, fix workbook searchability, or comply with regulatory standards.

You can practice the following techniques to fit different scenarios for character removal in Excel.

Using Flash Fill

Are you using Excel 2019 or later on your Windows PC and Excel for Mac 2016 or later on your Mac? You can use the Flash Fill tool to teach Excel an action with a pattern so it can reproduce the steps.

In the above dataset, you can remove the unnecessary trailing characters from the email addresses using Flash Fill.

Firstly, in B2, you’ll need to manually type in the email address except for the trailing characters *123.

Repeat the same step in B3.

Now, select B2 and B3 and drag down the fill handle to B6. The fill handle is the tiny green square at the bottom right corner of a cell. It only appears when you select a cell or a range of cells.

Excel will replicate the editing you’ve done in B2 and B3 and apply that through B4 and B6.

If you can’t find the fill handle, you can try the alternative way to use this method.

In B2, enter the correct format of the first email address manually by typing.

Now, select this cell and press Alt + A + F + F to apply the Flash Fill command from the Data Tools block in the Data tab.

Using the Text to Columns Tool

You can use the Text to Columns tool to separate the unwanted characters from a text string. Then, you can delete or hide the new column and thus achieve character removal in Excel.

Here, you choose a fixed character length for removal. So, the same rule will be applied to the entire column. So, if there’s a pattern of redundant characters in a dataset, this method will work.

For example, in the above dataset, you can remove the non-numeric characters at the end easily to keep the numerical serial numbers in the original column.

For each cell in column A, there are 5 non-numeric trailing characters, creating a pattern. The Text to Column Tool can capitalize on this to delete characters.

To try this method, select the source dataset.

Press Alt + A + E to bring up the Text to Columns wizard.

Choose Fixed width below the Original data type section. Click Next.

On the Data preview panel, click between the numeric and non-numeric characters you want to separate. A line will show up. You can drag it left or right to make fine adjustments. This line tells Excel from which point it should remove the non-numeric characters. Click Next.

You’ll see the 3rd dialog box of the wizard. Here, you can choose the number formatting for the dataset.

Click Finish.

Excel will keep the numerical part of the serial numbers in the source column and shift the non-numeric characters to the adjacent column on the right.

Using the Find and Replace Tool

This is yet another easy and intuitive method you should practice as a new to intermediate-level Excel user. If you’re an expert Excel user, you can use this technique on a small dataset for quick results.

I’m showing this method with an example of a word. However, it’d work similarly on single or multiple characters.

In the above dataset of a few text strings, I’ll demonstrate how to remove the highlighted characters effortlessly.

Press Ctrl + F to bring up the Find and Replace tool.

Click on the Replace tab.

In the Find what field, enter the target word or character.

Leave the Replace with field blank.

Click on the Replace All button.

Excel will remove the characters you entered in the Find what field and replace those with nothing since you left the Replace with field empty.

Using the SUBSTITUTE Function

If you prefer using Excel functions to do things, you must try this method.

Let me show you how can you remove all the occurrences of the character a from the words in the above dataset.

Select B2 and enter the following formula there:

=SUBSTITUTE(A2,"a","")

The above formula tells Excel to remove all the instances of the character a in the text string of A2.

You can customize the formula according to your own dataset. For example, replace a with b to remove all the instances of the character b.

Hit Enter to calculate the cell.

You should see the resulting text string without the character a.

Now, drag the fill handle down the column to apply the same formula to the rest of the cells of the dataset.

Sometimes, you might not want to delete all the a characters in the given cell. Instead, you’d want to delete the first instance. In this case, you need to enter the value for the instance number argument for the SUBSTITUTE formula. Here’s the revised formula:

=SUBSTITUTE(A2,"a","",1)

Using the TRIM Function

Suppose, there are redundant spaces in the text strings of your dataset. Here, you can use the TRIM function to delete these unnecessary characters.

In the above dataset, there are extra spaces between two words or between a word and an article. Correcting this grammatical mistake isn’t possible by manually editing the text strings, especially if the dataset is medium to big.

To fix the text strings, enter the following formula in B2.

=TRIM(A2)

Hit Enter to calculate the formula.

You should see the corrected text string with no redundant spaces.

Drag the fill handle down the column to fix the rest of the entries.

A drawback of this function is it can’t detect redundant spaces in between words.

For example, look at the above screenshot. There are incorrect spaces within the words.

If you apply the TRIM function, Excel won’t delete unwanted spaces within the words to correct the fault.

Using the CLEAN Function

Characters like apostrophes, null characters, line feeds, carriage returns, horizontal tabs, etc., don’t show up when you print your worksheet. However, these characters can influence data formatting and handling actions.

Before working on an imported dataset, which might contain unprintable characters, you can apply the CLEAN function to get rid of these characters.

For example, there are apostrophes in the above dataset. However, you can’t see or print these characters.

To remove apostrophes, enter the following formula in B2 and hit Enter:

=CLEAN(A2)

Excel will remove the apostrophe in the selected cell.

Now, drag the fill handle down the column to apply the same formula to the rest of the dataset.

The output of the CLEAN function is always in text format unless you wrap it around a different function or manually apply a number formatting rule after calculating the cell.

Using Power Query

You can use Power Query when importing data from a third-party database to clean the dataset before adding it to the Excel worksheet.

For external datasets, use the Get Data command in the Data tab.

A context menu will show up. Hover the mouse over the From Database option and select a source from the overflow menu, like From Analysis Services.

If your dataset is already in your worksheet, select it and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog box to import the dataset to Power Query.

Suppose, you need to remove the trailing characters, which is *123.

Go to the Transform tab. Click on the Text Column drop-down menu.

Within the Text Column context menu, click on Extract and select the Text Before Delimeter command.

Enter the delimiter character into the next dialog box. In this case, it’s the asterisk character. Click OK to apply.

Power Query will simply remove the unwanted characters as you can see in the above screenshot.

Click on the File tab and select the Close & Load To option.

Choose the destination worksheet and cell range in the Import Data dialog box and click OK.

This will export the transformed data from Power Query to an Excel sheet.

Using Excel VBA

If you want to automate various methods for removing characters in Excel, you can use Excel VBA.

Find below various scripts you can use to create VBA macros that’ll automatically remove characters according to your inputs.

Before moving ahead with the scripts, see this Excel tutorial for the steps to create a VBA macro in Excel:

📒 Read More: How To Use The VBA Code You Find Online

All set? Let’s explore the scripts now!

Remove Leading and Trailing Spaces

The following script will remove redundant space characters in the leading and trailing positions of a text string:

Sub RemoveSpaces()
    Dim inputRange As Range
    Dim outputRange As Range
    Dim cell As Range

    ' Prompt user to select input range
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the input cell range:", "Input Range", Type:=8)
    If inputRange Is Nothing Then Exit Sub ' Exit if cancelled
    On Error GoTo 0

    ' Prompt user to select output range
    On Error Resume Next
    Set outputRange = Application.InputBox("Select the destination cell range:", "Output Range", Type:=8)
    If outputRange Is Nothing Then Exit Sub ' Exit if cancelled
    On Error GoTo 0

    ' Check if the number of cells in both ranges are equal
    If inputRange.Cells.Count <> outputRange.Cells.Count Then
        MsgBox "The number of cells in the input range must match the destination range.", vbExclamation, "Error"
        Exit Sub
    End If

    ' Remove leading and trailing spaces
    For Each cell In inputRange
        outputRange.Cells(cell.Row - inputRange.Row + 1, cell.Column - inputRange.Column + 1).Value = Trim(cell.Value)
    Next cell

End Sub

Create a VBA macro using the script. Press Alt + F8 to launch Macro dialog box and select the RemoveSpaces macro.

Hit Run to execute the macro.

You’ll see an input box so you can choose the input dataset cell range from the active worksheet.

Another input box will ask you to enter the destination cell range.

Once you supply all the input data, Excel will remove the selected space characters and paste the clean dataset into the destination cell range.

Delete Numeric Characters

Suppose, you’ve got the above dataset. You want to remove the numeric characters and extract the text string part of the codes to a different column.

The following VBA script will help you achieve this automatically:

Sub DeleteNumericCharacters()
    Dim InputRange As Range
    Dim DestinationRange As Range
    Dim cell As Range
    Dim i As Integer
    Dim TextPart As String
    Dim NumericPart As String

    ' Select the input dataset
    On Error Resume Next
    Set InputRange = Application.InputBox("Select the input range:", Type:=8)
    If InputRange Is Nothing Then Exit Sub
    
    ' Select the destination range
    Set DestinationRange = Application.InputBox("Select the destination range:", Type:=8)
    If DestinationRange Is Nothing Then Exit Sub
    
    i = 1 ' Initialize counter for destination cells
    
    ' Loop through each cell in the input range
    For Each cell In InputRange
        TextPart = "" ' Reset TextPart for each cell
        NumericPart = "" ' Reset NumericPart for each cell
        
        ' Check each character in the cell
        For j = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, j, 1)) Then
                NumericPart = NumericPart & Mid(cell.Value, j, 1) ' Concatenate numeric characters
            Else
                TextPart = TextPart & Mid(cell.Value, j, 1) ' Concatenate non-numeric characters
            End If
        Next j
        
        ' Move text part to destination range and format as text string
        DestinationRange.Cells(i, 1).Value = TextPart
        DestinationRange.Cells(i, 1).NumberFormat = "@" ' Set format to text
        
        ' Replace original cell value with numeric part only
        cell.Value = NumericPart
        
        i = i + 1 ' Move to next cell in destination range
    Next cell
    
End Sub

The script will show the following input boxes:

• A prompt so you can choose the source dataset.

• The second prompt asks you to choose the destination cell range.

Excel performs the instructed tasks when you interact with the above dialog boxes. You can delete the numeric data column if you don’t need that anymore.

Eliminate Non-Numeric Characters

The following script will remove the non-numeric part from the selected dataset and extract the numeric part in a custom cell range.

Sub RemoveNonNumericCharacters()
    Dim InputRange As Range
    Dim OutputRange As Range
    Dim cell As Range
    Dim OutputRow As Integer
    
    ' Prompt user to select input range
    On Error Resume Next
    Set InputRange = Application.InputBox("Select the input range:", Type:=8)
    If InputRange Is Nothing Then Exit Sub ' Exit if cancelled
    
    ' Prompt user to select output range
    Set OutputRange = Application.InputBox("Select the output range:", Type:=8)
    If OutputRange Is Nothing Then Exit Sub ' Exit if cancelled
    On Error GoTo 0
    
    ' Initialize the row for output data
    OutputRow = OutputRange.Row
    
    ' Loop through each cell in the input range
    For Each cell In InputRange
        ' Remove all non-numeric characters
        Dim CleanValue As String
        CleanValue = ""
        For i = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, i, 1)) Then
                CleanValue = CleanValue & Mid(cell.Value, i, 1)
            End If
        Next i
        
        ' Check if there is any numeric value to output
        If Len(CleanValue) > 0 Then
            ' Output the cleaned numeric value to the output range and format as number
            With OutputRange.Worksheet.Cells(OutputRow, OutputRange.Column)
                .Value = CDbl(CleanValue)
                .NumberFormat = "0"
            End With
            
            ' Move to next row in output range
            OutputRow = OutputRow + 1
        End If
    Next cell
    
End Sub

The script will walk you through with these input dialogs:

• Select your dataset

• Select a destination

Excel extracts the numeric data to the destination. You can delete the original column if you want to.

A Custom VBA Script

This script will help you remove a specified character from a cell or cell range completely. Also, you can choose to remove the character from a certain position.

Sub RemoveCharacters()
    Dim targetRange As Range, outputRange As Range, characterToRemove As String, messageResponse As Integer, positions As String, positionArray() As String, i As Integer, cell As Range
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the target dataset:", Type:=8)
    If targetRange Is Nothing Then Exit Sub
    Set outputRange = Application.InputBox("Select the output cell range:", Type:=8)
    If outputRange Is Nothing Then Exit Sub
    characterToRemove = Application.InputBox("Enter the character you want to remove:", Type:=2)
    If characterToRemove = "" Then Exit Sub
    messageResponse = MsgBox("Do you want to remove all instances of '" & characterToRemove & "'?", vbYesNoCancel + vbQuestion, "Remove All Instances?")
    If messageResponse = vbCancel Then Exit Sub
    If messageResponse = vbYes Then
        For Each cell In targetRange
            cell.Value = Replace(cell.Value, characterToRemove, "")
            outputRange.Value = targetRange.Value
        Next cell
    Else
        positions = Application.InputBox("Enter the positions of the character to remove (e.g., 1,2,3):", Type:=2)
        If positions = "" Then Exit Sub
        positionArray = Split(positions, ",")
        For Each cell In targetRange
            Dim tempString As String: tempString = cell.Value
            For i = UBound(positionArray) To 0 Step -1
                Dim pos As Integer: pos = 0
                If IsNumeric(positionArray(i)) Then pos = VBA.CInt(positionArray(i))
                If pos > 0 Then tempString = RemoveCharacterAt(tempString, characterToRemove, pos)
            Next i
            cell.Offset(0, outputRange.Column - targetRange.Column).Value = tempString
        Next cell
    End If
End Sub

Function RemoveCharacterAt(ByVal text As String, ByVal character As String, ByVal instanceNumber As Integer) As String
    Dim i As Integer, count As Integer
    i = 1
    count = 0
    Do While i <= Len(text)
        If Mid(text, i, 1) = character Then count = count + 1
        If count = instanceNumber Then
            RemoveCharacterAt = Left(text, i - 1) & Mid(text, i + 1)
            Exit Function
        End If
        i = i + 1
    Loop
    RemoveCharacterAt = text
End Function

The VBA macro will guide you through these input boxes:

• An input box to choose input data.

• A prompt so you can select the destination.

• An input box to enter the target character, like a.

• A message box asking you to choose from all or select a few.

• An input box so you can enter the position. It starts from the left. 1 means the first instance of a, 2 means the second, and so on. For multiple positions, enter comma-separated inputs, like 1,2.

Excel will populate the results in the destination.

Conclusions

So far, you’ve learned how to remove characters in Excel using command buttons, functions, Power Query, and Excel VBA.

You can acknowledge in the comment box if the article helped you.