3 Ways to Remove Specific Characters in Excel

Sometimes the data that we work with may contain unwanted characters that can make it difficult to read, sort, or analyze. Whether it is extra spaces, dashes, commas, or any other character, it can be frustrating to deal with. Luckily, Excel offers a variety of ways to remove specific characters from a cell or a range of cells. Thus, you can clean up your data and make it more manageable. In this article, I will show you 3 different ways to remove specific characters in Excel.

Introduction to Dataset

In this article, I will be using a list of social security numbers or SSNs in a column named Raw Data as an example dataset. The SSNs have two hyphens (-) as special characters in each number. I want to remove these hyphens (-) and want no spaces between the numbers.

Let me break down to you how I removed these specific characters in Microsoft Excel.

Easiest Way to Remove Specific Characters in Excel

There are some formulas that can help you remove characters. But there is another way to do the work without applying any function. You can easily remove specific characters with the Find and Replace feature in Excel.

Usage Guide

Step_1: At first, select the cell range A2 to A10.

Step_2: Then go to the Home tab.

Step_3: Now right-click on the Find and Select drop-down menu. You can find this option in the Editing group.

Step_4: Then select the option named Replace from the drop-down menu.

You will see the Find and Replace Dialog Box popping up on the worksheet.

Step_5: Open the Replace tab.

Step_6: inside the Find What box, type “-”.

Step_7: Skip the Replace With box.

I did not enter any characters in the Replace With box because I don’t want anything replacing the hyphens (-). For example, if you want a slash (/) in replace of the hyphens (-), you can enter a slash (/) there.

Step_8: Now hit the Replace All button.

Step_9: Finally, click on the Close button.

You will see a window with this message.

Step_10: Click on the OK button.

Final Result

You can see that now there are no special characters in the cell range.

Alternative Way #1: Using SUBSTITUTE Function to Remove Specific Characters in Excel

The SUBSTITUTE function is entitled to replace a specified text string with a new text string in a given cell or range of cells.

Syntax

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

Formula

=SUBSTITUTE(A2,”-“,””)

Step_1: Take an empty column to enter the results. I used column B and named it Result.

Step_2: Write the formula in cell B2.

Step_3: Press the ENTER key.

Step_4: After that place the cursor on the bottom-right corner of cell B2. The Fill Handle button will appear.

Step_5: Then double-click on the Fill Handle button.

Final Result

Using the SUBSTITUTE function is another fastest way to remove specific characters in my opinion.

---

Formula Explanation

Text: It is the cell reference that you want to change or modify.

Old_text: It is the mention of the characters that you want to replace with something new.

New_text: It is the mention of the new characters that you want to replace the old_text with.

[instance_num]: It is an optional argument. It defines the occurrence of the old_text that you want to replace. If you omit this argument, all instances of the old_text will be replaced.

---

Alternative Way #2: Using MID and CONCATENATE Functions to Remove Specific Characters in Excel

The MID function is commonly used to extract parts of the original text string that you want to keep. Using this function, you can specify the starting position and also choose how many characters to remove. On the other hand, the CONCATENATE function is used to combine two or more text strings into a single cell.

Syntax

=MID(text, start_position, num_characters)

=CONCATENATE(text1, [text2], …)

Formula

=CONCATENATE(MID(A2,1,3),MID(A2,5,2),MID(A2,8,LEN(A2)))

Usage Guide

Step_1: Select cell B2.

Step_2: Then enter the formula in cell B2.

Step_3: Now press the ENTER key.

Step_4: After that, place the cursor on the bottom right corner of cell B2. The cursor sign will appear as the Fill Handle (+) button.

Step_5: Now double-click on the Fill Handle (+) button.

Final Result

This way, by adjoining some small and easy functions, you can make your custom-made formulas. It works fine, just like the other techniques.

---

Formula Explanation

The first MID function extracts the first 3 characters of the original text string (“375“) of cell A2. Here, 1 indicates the starting number where we start counting the characters. 3 indicates the position of the character.

In the second MID function, 5 indicates the starting position of the second set of characters. 2 indicated the number of characters to keep.

The third MID function extracts the remaining characters of the original text string, starting from position 8. And 3 indicating the 3 characters to store.

Lastly, I applied the CONCATENATE function is used to combine the extracted parts into a single text string.

---

Conclusion

I have discussed three super easy ways to remove any sort of specific characters in Excel. The characters could be either numerical or text-type. In both cases, these techniques will work. I hope you find this article helpful. To find out more about removing characters-related articles, click here to read our other blogs.  Please feel free to comment in the comment box if you have any confusion regarding this topic. Thank you and have a good day!