This post will guide you how to remove a specific character from text cell in Excel. How do I remove certain character from a cell with a formula in Excel 2013/2016.

Assuming that you have a list of data in range B1:B5, in which contain text string values. And you want to remove a specific character if it appears in a given cell. For example, if wish to remove all hash (#) character from your range, how to do it. This post will show you two methods to remove specific character.

1. Remove Specific Character Using Find and Replace function

Step1: select one range where you want to remove certain character. Such as: B1:B5.

Step2: go to Home Tab, and click on the Find & Select command under Editing group. And choose Replace from the context menu. And the Find and Replace dialog will open.

Step3: Type “#” into the Find What text box, and keep the Replace with text box as blank.

Step4: click on Replace All button. And a prompt dialog will tell you that how many characters are replaced in your selected range of cells. Click Close button to close the Find and Replace dialog box.

2. Remove Specific Character Using Formula

You can also use an Excel formula based on the SUBSTITUTE function and the CHAR function to accomplish the same result of removing specific character. Like this:

=SUBSTITUTE(B1,CHAR(35),””)

Type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle down to other cells to apply this formula.

You would see that this formula can be used to remove all hash characters from a cell.

3. Remove Specific Character from Text Cell using VBA Code

For the third method, let’s delve into the power of VBA  code to remove specific characters from text cells dynamically. This method provides advanced customization, allowing you to create a tailored solution for your specific text cleaning requirements using the flexibility of VBA.

Press ‘Alt + F11‘ to open the Visual Basic for Applications editor.

Right-click on the project in the editor, select ‘Insert,’ and choose ‘Module‘ to add a new module.

Copy and paste the following VBA code into the module:

Sub RemoveSpecificCharacterWithPrompt()
    On Error Resume Next

    ' Prompt user to select source range
    Dim sourceRange As Range
    Set sourceRange = Application.InputBox("Select the source range with text to modify", Type:=8)

    ' Prompt user to select destination range
    Dim destRange As Range
    Set destRange = Application.InputBox("Select the destination range to place the results", Type:=8)

    On Error GoTo 0

    ' Check if both source range and destination range are selected
    If Not sourceRange Is Nothing And Not destRange Is Nothing Then
        Dim cell As Range
        Dim resultText As String

        ' Loop through each cell in the selected source range
        For Each cell In sourceRange
            ' Replace specific character in each cell's value
            resultText = Replace(cell.Value, "#", "")

            ' Find the corresponding cell in the destination range and place the result
            destRange.Cells(cell.Row - sourceRange.Row + 1, cell.Column - sourceRange.Column + 1).Value = resultText
        Next cell
    Else
        MsgBox "Operation canceled. Please select both source range and destination range.", vbExclamation
    End If
End Sub

Replace “YOUR_SPECIFIC_CHARACTER” in the VBA code with the character you want to remove. Such as: hash character.

Close the editor and press ‘Alt + F8‘

Choose ‘RemoveSpecificCharacterWithPrompt‘ and click ‘Run‘

A prompt will appear, asking you to select the source range containing text to modify. Click and drag to select the desired range,

then click ‘OK‘ button, Another prompt will appear, asking you to select the destination cell to place the last result. Click on the desired cell,

then click ‘OK‘ button, the specific character will be removed from each cell in the selected source range, and the last result will be placed in the specified destination cell.

4. Video: Remove Specific Character from Text Cell

This Excel video tutorial where we’ll tackle the task of removing specific characters from text cells. Join us as we explore three effective methods – the first using a formula with the SUBSTITUTE function, the second employing the ‘Find and Replace’ feature, and the third leveraging VBA code.

5. Related Functions

• Excel Substitute function
  The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
• Excel CHAR function
  The Excel CHAR function returns the character specified by a number (ASCII Value). The syntax of the CHAR function is as below: =CHAR(number)….