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.

Remove Numeric Characters from Cells in Excel.xlsx

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.

Table of Contents

Remove Numeric Characters from Cells in Excel Using the VBA Code

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

Here’s the steps to follow for removing the numeric characters in Excel:

Go to the Developer tab.
Secondly, click on the Visual Basic icon from the Code group. The VBA window will pop up on the screen.

From the Project Panel, right-click on the sheet name (Sheet1). A drop-down menu will appear instantly.
From the menu, go to Insert > Module.
Now copy the VBA code from above in this article.

Paste it inside the Module window and close the VBA window.
Come back to the Excel sheet again.
Now, type the following formula inside cell B2: =RemoveNumbersFromCell(A2)

Hit the ENTER key and drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.

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

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

Applicable for: any combination of alphabetic and numeric characters.

Remove Numeric Characters from Cells in Excel by Applying Flash Fill

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.

Here’s the process of applying Flash Fill to remove numeric characters in Excel:

Enter each alphabetic character from the raw data cell into a separate cell.

Now, go to the Data tab.
From the Data Tools group, choose the Flash Fill command.

The Flash Fill feature will swiftly remove the numeric characters from the cells in column Sample Data, revealing the alphabetic characters in column Cleaned Data.

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

Applicable for: any combination of alphabetic and numeric characters.

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

You can follow this method to quickly remove numeric characters from a cell range if the range follows a consistent pattern.

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).

Follow this usage guide to apply the method:

Select the cell range A2:A10.

Now go to the Home tab.
Then take the cursor to the Editing group.
From the Find and Select drop-down menu, click on Replace. This will open the Find and Replace dialog box.

Now, in the Find What box, type -* and keep the Replace With box empty.
Finally, click on the Replace All button.
Hit OK.

The selected cell range does not contain any numeric characters or punctuation marks anymore.

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

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

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

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

Remove Numeric Characters from Cells in Excel with Power Query

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 numeric characters are positioned at the end of the strings, and the alphabetic characters are at the start.

Use this guide to apply Power Query to remove the numeric characters in Excel:

Select the cell range A1:A10 and go to the Data tab.

Now, select the From Table command from the Get & Transform group.
Check the cell range in the Create Table window and give a tick mark on the My table has headers box.
Hit OK. Then, the Power Query Editor will pop up.

Now click on the Split Column drop-down menu.
Select the By Non-Digit to Digit command.
Now click on the top-left command named Close & Load. It will take you to a new Excel sheet containing the two new columns.

Select the column, Sample Data.2.
From the Home tab, click on the Delete drop-down list and select the Delete Cells command.

The sheet now only contains the column with alphabetic characters, allowing you to easily distinguish between different characters in Excel using Power Query.

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.

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

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)

Formula Explanation

The formula in Excel is designed to trim off the rightmost numeric digits from the content of cell A2. It achieves this by appending the digits “0123456789” to the original content of A2 and using the FIND function to locate the first occurrence of any numeric character within the combined string. The MIN function then identifies the minimum position of a numeric character, and this position is subtracted from the length of the original content. Adding 1 ensures that the result includes the position of the first numeric character. Finally, the LEFT function extracts the left portion of the original content up to the calculated position, effectively removing the rightmost numeric digits. This formula is useful when you need to extract the non-numeric part of a cell that may contain a mix of text and numbers.

Take a look at the step-by-step guide to remove numeric characters in Excel:

Take an empty column beside the Sample Data column.
Type the formula in cell B2: =LEFT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″,1))+1)
Now press the ENTER key.

Double-click on the Fill Handle icon.

This method is really simple if you learn how the formula works, even though it may look complicated at first. Additionally, you may use the same method to apply the RIGHT function if you want to keep the digits and exclude the alphabetic characters.

Applicable for: Only Alphabetic-Numeric Structure.

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!

Frequently Asked Questions (FAQs)

Why would I need to remove numeric characters in Excel?

Removing numeric characters in Excel can be useful in various scenarios, such as when you want to clean up data, extract specific information, or perform calculations that involve text values without numeric interference.

Can I remove numeric characters from an entire column at once?

Yes, you can apply the same formula or technique to an entire column using cell references. The article explains how to remove numeric characters not just from a single cell but from a range or column.

What if I only want to keep the numeric characters and remove the text?

The article covers this aspect as well. It provides instructions on how to extract numeric characters and discard non-numeric characters, allowing you to keep only the numbers in a cell.

Can I remove numeric characters based on certain conditions or criteria?

Yes, the article explores how to remove numeric characters based on specific conditions using functions like IF and logical operators. This allows you to customize the removal process according to your requirements.

Will removing numeric characters affect other data in my spreadsheet?

No, the methods explained in the article are designed to selectively remove numeric characters from the specified cells or ranges, leaving the rest of your data intact.

How can I remove numeric characters while preserving formatting or special characters?

The article provides insights into handling special characters and formatting considerations while removing numeric characters, ensuring that your data retains its original structure.

Related Articles

Rate this post