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.

Sample dataset in Excel

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

Applicable for any combination of alphabetic and numeric characters.

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.

Shortcut: Press the Alt+F11 or Alt+Fn+F11 key.

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.

Open Module in VBA window in Microsoft Excel

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

Use VBA codes to Remove Numeric Characters from Cells in Excel

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)

Generate formula with VBA code to Remove Numeric Characters from Cells in Excel

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.

Dragging the Fill Handle to copy down the formula generated from VBA code

Final Result

Result of using the VBA to Remove Numeric Characters from Cells in Excel

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

Applicable for any combination of alphabetic and numeric characters.

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.

Note: The process is case-sensitive. Please remember to enter the letters as they are present in the original cells.

Step_2: Now, go to the Data tab.

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

Using Flash Fill feature in Excel

Final Result

Use Flash Fill to Remove Numeric Characters from Cells in Excel

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

Applicable for: Text-Delimiter-Number or, Number-Delimiter-Text Combination.

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

Note: The selected cell range must have a delimiter (-,/,:, etc.) in between the text and numeric characters. Otherwise, the technique will not work properly.

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.

Open Find and Replace Dialog Box to Remove Numeric Characters from Cells in Excel

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

Shortcut: Press CTRL+H to open the Find and Replace dialog box

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.

Use Find and Replace to Remove Numeric Characters from Cells in Excel

Note: If you use a different delimiter then input that delimiter first and insert the asterisk symbol (*).

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

Applicable for: Cells with a Fixed Structure (Alphabetic-Numeric/Numeric-Alphabetic Pattern) 

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.

Opening Power Query in Excel

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.

Use Power Query to Remove Numeric Characters from Cells in Excel

Note: I selected the option “By Non-Digit to Digit” since my chosen cell range has a fixed structure where non-digits are positioned before the digit. You can choose a different option if your cell range has a different structure.

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

Split Column in Power Query in Excel

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

Closing power query in Excel

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.

Using Delete Cells command in Excel

Final Result

Result of Using Power Query to Remove Numeric Characters from Cells in Excel

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

Applicable for: Only Alphabetic-Numeric Structure

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.Using the LEFT, LEN, MIN, and FIND Functions to Remove Numeric Characters from Cells in Excel

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!

Related Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *