How to Remove Characters in Excel: 7 Best Ways

by | Excel

Ready to remove characters in your Excel spreadsheet? Well, you’re in luck, as there are multiple great ways to do it, depending on your specific needs.

To remove characters, you can use the Find and Replace method in Excel. Select the data from your worksheet, and open Find and Replace by clicking Ctrl + H. Specify the characters you want to remove and leave the replace section empty. Click replace all and Excel will remove all the characters you specified.

You can use other methods as well to remove characters in Excel such as the Text to Columns method, SUBSTITUTE and REPLACE functions, and the flash fill method.

In this article, we’ll explore 7 great techniques and formulas that will allow you to easily remove characters in Excel.

Be sure to read through them all, understand them, and select which one works best for your use case.

Let’s get into it.

How to Remove Characters in Excel

7 Methods to Remove Characters in Excel

In this section, you will find 7 methods of removing characters in Excel.

The dataset we’ll use for demonstration is given below:

Dataset under analysis

The 7 methods are:

  1. Using Find and Replace

  2. Using The SUBSTITUTE Function

  3. Using The REPLACE Function

  4. Using LEFT, RIGHT, And MID Functions

  5. Using Flash Fill

  6. Using Power Query

  7. Using VBA

1. How to Use Find and Replace to Remove Characters

You can use the find and replace feature in Excel to remove characters in Excel.

Follow the steps given below to remove characters using this method:

Step 1: Select The Data

First, select the cells or range where you want to remove characters.

In our case, since the names column has characters that we want to get rid of, we’ll select the names column.

Step 2: Open Find and Replace

Click on the Home tab on the Ribbon. In the Editing group, find and click on Find & Select.

From the dropdown menu, choose “Replace” (or use the keyboard shortcut Ctrl + H).

Opening find and replace

Step 3: Enter Characters to Remove

In the “Find what” field of the Replace dialog box, enter the character or characters you want to remove and leave the Replace With box empty.

We will enter “/” and “]” in the Find what box.

Configuring the find and replace menu

Click Replace All and Excel will remove all the characters in the Find what box from your selected range.

Specified characters removed from Names column

2. How to Use The SUBSTITUTE Function to Remove Characters

You can also use the substitute function to remove characters in Excel. Follow the steps given below to remove characters using this method:

Step 1: Identify the Text and Character

First, determine the text from which you want to remove a character and identify the specific character or string you want to remove.

Suppose we want to remove brackets from the Names column, as shown in the image below:

Dataset under analysis

Step 2: Use the SUBSTITUTE Function

The syntax for the SUBSTITUTE function is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

text is the original text. old_text is the character or string you want to remove. new_text is what you want to replace old_text with. To remove characters, this should be an empty string “”.

[instance_num] is optional and specifies which occurrence of old_text you want to replace. If omitted, all instances are replaced.

For example, to remove the brackets, we can use the following formula:

=SUBSTITUTE(A1, "]", "")

In Excel, you can implement the formula like the following:

Writing formula for substitue function

Step 3: Using Fill Handle to Apply Formula to Entire Column

After writing the formula, press Enter and Excel will display the result.

Use the fill handle to apply the formula to the entire column.

New column with names created

The new column no longer has any uncleaned entries.

3. How to Use REPLACE Function to Remove Characters in Excel

The REPLACE function in Excel is used to replace part of a text string with a different text string.

You can adapt it to remove characters by replacing them with an empty string.

Follow the steps below to remove characters using this method:

Step 1: Identify The Text And Character

First, identify the range of cells that you want to remove a particular character from.

Step 2: Understand The Syntax

The syntax of the REPLACE function is:

REPLACE(old_text, start_num, num_chars, new_text)

old_text is the original text. start_num is the position of the first character in the old text that you want to replace.

num_chars is the number of characters in the old text that you want to replace. new_text is the text that will replace the characters defined by start_num and num_chars. To remove characters, set this to an empty string “”.

Step 3: Apply the REPLACE Function

Apply the REPLACE funtion.

In our case, the REPLACE formula will be:

=REPLACE(A1, 9, 1, "")

In Excel, you can implement the formula like the following:

Writing formula for replace function

Note that you can not use the fill handle to automatically apply the formula to the rest of the column.

This is because you have to manually change the indexes for each row.

After you write the formula, press Enter, and Excel will display the name with no special characters.

Name with special characters removed

4. How to Use LEFT, RIGHT, And MID Functions to Remove Characters

The LEFT, RIGHT, and MID functions in Excel are used to extract specific portions of text from a cell.

These functions do not directly remove characters. However, you can use them to isolate and discard parts of a text string.

The purpose of each function is listed below:

  • LEFT Function: Extracts a specified number of characters from the start (left side) of a text string.

  • RIGHT Function: Extracts a specified number of characters from the end (right side) of a text string.

  • MID Function: Extracts a specific number of characters from a text string, starting at the position you specify.

For example, to clean the names and remove the “]” character in the dataset, you can use a combination of these functions.

In our case, the formula will be:

=LEFT(A2, FIND("]", A2) - 1) & RIGHT(A2, LEN(A2) - FIND("]", A2))

The above formula works by first using LEFT to get all the text before the “]” and then using RIGHT to get all the text after the “]”. It then combines the two parts to form the cleaned text without the unwanted characters.

Writing Formula for LEFT, RIGHT, MID Functions

Click Enter, and Excel will display the result.

You can now apply the formula to the rest of the column by using the fill handle.

Names column cleaned

5. How to Use Flash Fill to Remove Characters

Flash Fill is a smart tool that automatically fills in data for you based on a pattern it recognizes from your input.

You can use it to remove characters from text without needing formulas.

Follow the steps below to remove characters using this method:

Step 1: Manually Input the Desired Result for the First Item

In the cell adjacent to the first item you want to change, type the text as you want it to appear without the unwanted characters.

For example, if the first name in your list is “James Sm]ith” and you want to remove “]”, manually type “James Smith” in the next cell.

 Manually Input the Desired Result for the First Item

Step 2: Initiate Flash Fill

Select the cell where you’ve just typed the corrected text.

Go to the Data tab on the Ribbon.

Click on Flash Fill in the Data Tools section, or simply press Ctrl + E.

Excel will automatically fill down the rest of the cells based on the pattern it recognized from your manual entry.

Initiating the Flash Fill

6. How to Use Power Query to Remove Characters

You can also use Power Query to remove characters from your data.

Power Query allows for more advanced data cleaning and manipulations.

Follow the steps below to remove characters using Power Query:

Step 1: Load Your Data into Power Query

Select any cell within your dataset.

Go to the Data tab on the Ribbon.

Click on From Table/Range in the Get & Transform Data group.

Loading Data to Power Query

If your data isn’t already in a table, Excel will prompt you to create one.

Prompt to create table

Step 2: Remove Characters Using Transformations

Once your data is loaded into Power Query, the Power Query Editor window will open.

Select the column from which you want to remove characters.

Go to the “Transform” tab.

Click on the “Replace Values” option.

Navigating to replace values menu

In the Replace Values menu, enter the characters you want to remove and leave the Replace With box empty.

Configuring the Replace Values menu

Click OK, and Power Query will remove the specified characters.

Specified characters removed

7. How to Use VBA to Remove Characters

VBA allows you to automate the process of removing characters from strings in your cells.

To remove characters using VBA, follow the steps given below:

Step 1: Open the VBA Editor

Press Alt + F11 to open the VBA Editor.

Step 2: Insert a New Module

In the VBA Editor, right-click on any of your workbook’s names in the “Project-VBAProject” pane.

Choose Insert > Module. This adds a new module to your project.

Inserting a new module

Step 3: Write the VBA Code

In the new module window, you can write a VBA custom function.

In our case, since we want to remove ] character from the Names column, our function will be:

Sub RemoveCharacters()
    Dim rng As Range
    Dim cell As Range
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select cells", Type:=8)
    
    For Each cell In rng
        cell.Value = Replace(cell.Value, "]", "")
    Next cell
End Sub

This script sets up a subroutine called RemoveCharacters. It asks the user to select a range of cells, and then it goes through each cell in that range, replacing the “]” character with an empty string.

Writing the VBA Code

Step 4: Run the VBA Script

Press Alt + F8 to open the scripts menu, and run the script you just created.

Running the VBA script

Excel will ask you for the range of cells you want the character removed from.

Selecting a cell range

Click OK, and Excel will remove the specified characters from the Names column.

Special characters removed

Learn how to count distinct values in Excel by watching the following video:

Final Thoughts

As you explore the world of data manipulation, you’ll find that removing characters in Excel is a fundamental skill that can be applied in various scenarios. From cleaning up data to extracting specific information, the ability to remove characters efficiently can save you time and ensure the accuracy of your analysis.

Excel offers a variety of functions and techniques to cater to your specific needs. By learning how to remove unwanted characters, you’re taking a significant step towards cleaner, more readable data. This skill ensures that your data is not just presentable but also reliable for analysis or reporting.

Each method we’ve discussed adds a layer of flexibility to your Excel toolkit. From the simplicity of Find and Replace to the sophistication of Power Query or VBA, you’re equipped to tackle a wide range of data cleaning challenges.

Frequently Asked Questions

In this section, you will find some frequently asked questions you may have when removing characters in Excel.

Close-up image of an analytics report

How do I remove the first character from a cell in Excel?

To remove the first character from a cell in Excel, you can use the RIGHT function combined with the LEN function.

For example, if your text is in cell A1, the formula =RIGHT(A1, LEN(A1)-1) will remove the first character.

This formula works by taking the length of the string in A1, subtracting 1, and then using RIGHT to return the remainder of the string.

What is the formula to remove the last character in a cell?

To remove the last character from a cell in Excel, you can use the LEFT function combined with the LEN function.

For instance, if the text is in cell A1, the formula =LEFT(A1, LEN(A1)-1) will remove the last character.

This formula calculates the length of the string in A1, subtracts 1, and then uses LEFT to return the string except for its last character.

How to remove specific characters from a string?

To remove specific characters from a string in Excel, you can use the SUBSTITUTE or the trim function. This function replaces occurrences of a specified character with another character or an empty string.

For example, if you want to remove the character “X” from a string in cell A1, you would use the formula =SUBSTITUTE(A1, “X”, “”).

This formula searches for all instances of “X” in the text in cell A1 and replaces them with nothing, effectively removing them. If you want to apply the formula to multiple cells, you can use the fill handle feature.

How to remove non-numeric characters in Excel?

To remove non-numeric characters from a text string in Excel, you can use a combination of the MID, ROW, CODE, and LEN functions.

Related Posts