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.
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:
The 7 methods are:
Using Find and Replace
Using The SUBSTITUTE Function
Using The REPLACE Function
Using LEFT, RIGHT, And MID Functions
Using Flash Fill
Using Power Query
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).
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.
Click Replace All and Excel will remove all the characters in the Find what box from your selected range.
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:
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:
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.
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:
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.
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.
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.
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.
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.
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.
If your data isn’t already in a table, Excel will prompt you to create one.
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.
In the Replace Values menu, enter the characters you want to remove and leave the Replace With box empty.
Click OK, and Power Query will remove the specified characters.
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.
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 SubThis 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.
Step 4: Run the VBA Script
Press Alt + F8 to open the scripts menu, and run the script you just created.
Excel will ask you for the range of cells you want the character removed from.
Click OK, and Excel will remove the specified characters from the Names column.
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.
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.
