5 Ways to Remove Numeric Characters from Cells in Excel

Sometimes it is not uncommon to experience situations where numeric characters need to be removed from cells that have both text and numerical strings. You may see various situations where numerical values are present in various arrangements in the data set, and you would need to clean them up. In this article, I am going to describe 5 different ways that will help you remove numeric characters from the cells in Excel.

Introduction to the Dataset

In the following dataset, I have 2 columns (Sample Data and Cleaned Data). Notice that in the column Sample Data, both alphabetical and numerical characters are present at different positions. Even the number of total digits is not the same in all cells.

You can also notice cell A5 contains only the numerical characters, and cell A6 has only the alphabetical characters.

Now, I will show you how you can remove the numeric characters from the cells of an Excel sheet.

Easiest Way to Remove Numeric Characters from Cells in Excel

If you are comfortable with the VBA in Excel, I believe you will find this method very easy to use. This method is best for removing any numeric characters with any arrangement from the cells in Excel.

This piece of VBA code can smoothly remove the numeric characters from the cells in Excel. Just insert the code inside a new Module in your Visual Basic Editor and it is good to go.

Function RemoveNumbersFromCell(gTxt As String) As String

With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbersFromCell = .Replace(gTxt, "")
End With

End Function

Usage Guide

Step_1: First, go to the Developer tab.

Step_2: Secondly, click on the Visual Basic icon from the Code group.

The VBA window will pop up on the screen.

Step_3: From the Project Panel, right-click on the sheet name.

The sheet name that I am working with in my workbook is Sheet1 (Case #1).

A drop-down menu will appear instantly.

Step_4: From the menu, go to Insert, then select Module. It will add a Module to your sheet.

Step_5: Now copy the VBA code from above in this article.

Step_6: Paste it inside the Module window.

Step_7: Close the VBA window.

You will come back to the Excel sheet again.

Step_8: Now type the following formula inside cell B2.

=RemoveNumbersFromCell(A2)

Step_9: Hit the ENTER key.

Step_10: Finally, drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.

Final Result

This way, you can quickly remove the numeric characters present in your Excel sheet, no matter where the numerical value is located.

Alternative Way #1: Use Flash Fill to Remove Numeric Characters from Cells in Excel

If you want to avoid using the VBA codes, you can try this alternative method. This technique will work on any type of text string and will give you the same result as the previous method with the help of the Flash Fill feature.

Usage Guide

Step_1: First, manually enter each alphabetic character exactly as it is present in cell A2 inside cell B2.

Step_2: Now, go to the Data tab.

Step_3: Finally, click on the Flash Fill command from the Data Tools group.

Final Result

The Flash Fill will instantly trim out the numeric characters for you from the cells of column Sample Data and display the alphabetic characters in column Cleaned Data.

Alternative Way #2: Use Find and Replace to Remove Numeric Characters from Cells in Excel

If your selected cell range follows a fixed pattern, it will be a faster way to remove numeric characters from the cell range.

Here as an example, I have used a dataset that has the Text-Delimiter-Number pattern.

Notice, the number of numerical characters is not the same in all cells. Like, cell A2 has a 9-digit number string (RD-336011225), cell A3 has a 4-digit number string (SG-2873), and cell A5 carries a 6-digit number string (RR-477500).

Usage Guide

Step_1: First, select the cell range A2:A10.

Step_2: Now go to the Home tab.

Step_3: Then take the cursor to the Editing group.

Step_4: From the Find and Select drop-down menu, click on Replace.

The Find and Replace dialog box will show up in front of the Excel sheet.

Step_5: Now, in the Find What box, type -*.

Step_6: Keep the Replace With box empty.

Step_7: Finally, click on the Replace All button.

A window showing the result of total replacements will come up on the screen.

Step_8: Hit OK.

Final Result

There are no longer any numeric characters, and there are also no punctuation marks in the selected cell range.

Alternative Way #3: Use Power Query to Remove Numeric Characters from Cells in Excel

If you need to split the alphabetic and numeric characters of a column into two separate columns, then using the Power Query Editor will be the best solution for you. So basically, the technique does not remove the numeric characters from the original column. Instead, it separates the numeric characters from the text characters and places them in a new column.

To show you the technique with Power Query, I am going to use the following dataset.

Unlike the dataset of the previous method, this dataset does not have delimiters between the alphabetic and numeric characters. But it does have a fixed arrangement of characters. The alphabetic characters are placed at the beginning, and the numeric characters are placed at the end of the strings.

Usage Guide

Step_1: At first, select the cell range A1:A10.

Step_2: Then go to the Data tab.

Step_3: Now select the From Table command from the Get & Transform group.

A small window named Create Table will pop up on your Excel sheet.

Step_4: Check the cell range in the window.

Step_5: If your table has headers, give a tick mark on the My table has headers box.

Step_6: Hit OK.

Now the Power Query Editor will open in front of you.

Step_7: Now click on the Split Column drop-down menu.

Step_8: Select the By Non-Digit to Digit command.

You will see the column is separated into two columns, one carrying the alphabetic characters and the other carrying the numeric characters.

Step_9: Now click on the top-left command named Close & Load.

It will take you to a new Excel sheet containing the two new columns.

Now, if you want to remove the numeric characters, you can erase them from the sheet.

Step_10: Select the column, Sample Data.2.

Step_11: From the Home tab, click on the Delete drop-down list.

Step_12: Finally, select the Delete Cells command.

Final Result

Only the column with the alphabetic characters remains on the sheet now. This way, you can separate different characters from the cells in Excel using the Power Query.

Alternative Way #4: Use the LEFT, LEN, MIN, and FIND Functions to Remove Numeric Characters from Cells in Excel

With the help of the following four functions, the FIND function, MIN function, LEN function, and LEFT function, I have created a formula to remove the numeric characters from the cells in Excel.

Syntax

=LEFT(text,LEN(text)-MIN(FIND(find_text, within_text, [start_num]))+1)

Formula

=LEFT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1))+1)

Usage Guide

Step_1: First, take an empty column beside the Sample Data column.

I chose column B and gave it a header, Cleaned Data.

Step_2: Then type the formula in cell B2.

Step_3: Now press the ENTER key.

Step_4: Now drag the Fill Handle icon down to copy the formula from cell B2 to cell B10.

Final Result

The formula may seem difficult at first, but once you understand how it works, you will find this method very simple. And also, if you want to keep the digits and remove the alphabetic characters, you can apply the RIGHT function using the same formula.

---

Formula Explanation

Find Function:

• {0,1,2,3,4,5,6,7,8,9} is used as the find_text. Since all numeric characters are made up of 0–9 characters. So if I input {0,1,2,3,4,5,6,7,8,9}, I can find out any numeric character that is present in the cell.
• Since cell A2 is our target cell, it is inserted as within_text. But I also have to mention the numeric characters in the within_text argument. That’s why I added & “0123456789”.
• 1 specifies the position of the within_text string where I want to begin the search.

MIN Function:

Inside the MIN function, I entered FIND({0,1,2,3,4,5,6,7,8,9}, A2&”0123456789″,1))+1 as number1.

The number1 indicates the first cell reference containing the first number of the range I want to assess.

LEN Function:

The text argument inside the LEN function is A2. It will count the number of characters in cell A2. After that, the positions of the numbers were subtracted using a minus sign (-).

LEFT Function:

• The text here is the cell reference of my target cell, A2.
• The num_char specified how many characters you want to get from the right side of the text. Here I used LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″,1)). But If the LEFT function finds the first cell reference, It removes it. For this reason, I further added +1 to consider the first cell reference in the output.

---

Conclusion

I hope now you can remove numeric characters with any arrangement from the cells in Excel. If you still find it difficult to remove the numeric characters from your datasheet, you can inform us in the comment box. Also, you can read more articles related to removing characters here. Thank you!