A very common problem while using MS Excel is how to remove numbers from text cells. For some reason, various systems mix up text and numbers intentionally or accidentally. This article will guide you to remove numbers from a cell in excel with some quick methods together with some suitable examples and proper illustrations.
Download Practice Book
You can download the free Excel template from here and practice on your own.
7 Effective Ways to Remove Numbers from a Cell in Excel
Let’s get introduced to our dataset first. I have placed some Product Names and their IDs’ in my dataset. The letters and the numbers have different meanings. For some reason, we want to remove the numbers from the Product IDs.
In the following methods, we will see how to remove the numbers from the cells with detailed explanations.
Method 1: Use Find & Replace with Wildcards to Remove Numbers from a Cell in Excel
In this method, we’ll remove those numbers using the Find & Replace command with the Wildcards.
In this stage, we have some rough numbers closed with parentheses and placed in the Products Names column. We will remove these numbers. So, let’s see the steps below.
Steps:
➤ Select the data range B5:B11.
➤ Press Ctrl+H to open Find & Replace command.
➤ Then type [*] in the Find what box and keep the Replace with box empty.
➤ After that, press Replace All.
Now you will spot that all the numbers with the products’ names are gone.
Method 2: Apply Find & Select Tool to Delete Numbers from a Cell in Excel
Here, look that there are two cells in the Product IDs column which contain numbers only. Now we’ll remove the numbers from the IDs’ cells using Find & Select command.
Step 1:
➤ Select the data range C5:C11.
➤ Then go to the Home tab > the Editing group > Find & Select > Go to Special
A dialog box will open up.
Step 2:
➤ Mark only Numbers from the Constants option.
➤ Press OK.
Now, look that the cells, only the numbers are highlighted.
Step 3:
➤ Later, just press the Delete button
on your keyboard.
Here it is. The numbers are removed.
Method 3: Use Excel Flash Fill to Remove Numbers from a Cell
It’s one of the easiest methods. Look that the product IDs, which are a mixture of text and numbers. We’ll remove the numbers from the cells using Excel Flash Fill.
Step 1:
➤ First, type only the text [not the digits] of the first cell to a new column adjacent to it.
➤ Then hit the Enter button.
Step 2:
➤ Select Cell D5.
➤ Now go to Data > Data Tools > Flash Fill.
Now you will observe that all the numbers are removed.
Method 4: Insert SUBSTITUTE Function to Remove Numbers from a Cell in Excel
In this method, we’ll do the task using the SUBSTITUTE function. The SUBSTITUTE function replaces an existing text with a new text in a string.
Now, let’s follow the steps below one by one.
Step 1:
➤ Write the formula given below in Cell D5–
=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[C5,1,""],2,""],3,""],4,""],5,""],6,""],7,""],8,""],9,""],0,""]
➤ Then just press the Enter button.
Step 2:
➤ Now double-click the Fill Handle icon and the formula will be copied down automatically.
Now you will see that the numbers are removed from the cells.
Similar Readings:
- How to Remove Subtotals in Excel [2 Easy Tricks]
- Data clean-up techniques in Excel: Replacing or removing text in cells
- How to Remove Formulas in Excel: 7 Easy Ways
Method 5: Combine TEXTJOIN, MID, ROW, LEN, and INDIRECT Functions to Erase Numbers from a Cell in Excel
Here, we’ll combine the TEXTJOIN, MID, ROW, LEN, and INDIRECT functions to remove numbers from a cell. The TEXTJOIN function is applied to join text from multiple strings together. The Mid function in excel is used to find out strings and return them from any mid part of the excel. The ROW function returns the row number for reference. The LEN function is a text function in excel that returns the length of a string/ text. The INDIRECT function returns a reference to a range.
Step 1:
➤ Type the formula in Cell D5–
=TEXTJOIN["",TRUE,IF[ISERR[MID[C5,ROW[INDIRECT["1:"&LEN[C5]]],1]+0],MID[C5,ROW[INDIRECT["1:"&LEN[C5]]],1],""]]
➤ Hit the Enter button.
Step 2:
➤ Then drag the Fill Handle icon to copy the formula.
👇 Formula Breakdown:
➥ ROW[INDIRECT[“1:”&LEN[C5]]]
It will find the resultant array list from the ROW and INDIRECT functions that returns as-
{1;2;3;4}
➥ MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1]
The MID function is applied to extract the alphanumeric string based on the start_num and num_chars arguments.And for the num-chars argument, we’ll put 1. After putting the arguments in the MID function, it will return an array like-
{“B”;”H”;”2″;”3″}
➥ ISERR[MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1]+0]
After adding 0, the output array is put into the ISERR function. It’ll create an array of TRUE and FALSE, TRUE for non-numeric characters, and FALSE for numbers. The output will return as-
{TRUE;TRUE;FALSE;FALSE}
➥ IF[ISERR[MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1]+0],MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1],””]
The IF function will check the output of the ISERR function. If its value returns TRUE, it will return an array of all the characters of an alphanumeric string. So we have added another MID function. If the value of the IF function is FALSE, it will return blank [“”]. So finally we’ll get an array that contains only the non-numeric characters of the string. That is-
{“B”;”H”;””;””}
➥ TEXTJOIN[“”,TRUE,IF[ISERR[MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1]+0],MID[B3,ROW[INDIRECT[“1:”&LEN[B3]]],1],””]]
The TEXTJOIN function will join all the characters of the above array and avoid the empty string. The delimiter for this function is set as an empty string [“”] and the ignored empty argument’s value is entered TRUE. This will give our expected result-
{BH}
Method 6: Join TEXTJOIN, IF, ISERR, SEQUENCE, LEN, and MID Functions to Delete Numbers from a Cell in Excel
We’ll combine another set of functions to do the task now. That’s are the TEXTJOIN, IF, ISERR, SEQUENCE, LEN, MID functions. The IF function is used to return one value if a condition is true and another value if it’s false. The ISERR function returns TRUE if the value is any error value, except #N/A. The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
Step 1:
➤ In Cell D5 write the given formula-
=TEXTJOIN["", TRUE, IF[ISERROR[MID[C5, SEQUENCE[LEN[C5]], 1] *1], MID[C5, SEQUENCE[LEN[C5]], 1], ""]]
➤ Press the Enter button to get the result.
Step 2:
➤ Then just apply the AutoFill option to copy the formula.
👇 Formula Breakdown:
➥ LEN[C5]
The LEN function will find the string length of Cell C5 that will return as-
{4}
➥ SEQUENCE[LEN[C5]]
Then the SEQUENCE function will give the sequential number according to the length that returns as-
{1;2;3;4}
➥ MID[C5, SEQUENCE[LEN[C5]], 1]
The MID function will return the value of that previous position numbers which results-
{“B”;”H”;”2″;”3″}
➥ ISERROR[MID[C5, SEQUENCE[LEN[C5]], 1] *1]
Now the ISERROR function will show TRUE if it finds an error otherwise it will show FALSE. The result is-
{TRUE;TRUE;FALSE;FALSE}
➥ IF[ISERROR[MID[C5, SEQUENCE[LEN[C5]], 1] *1], MID[C5, SEQUENCE[LEN[C5]], 1], “”]
Then the IF function sees TRUE, it inserts the corresponding text character into the processed array with the help of another MID function. And sees FALSE, it replaces it with an empty string:
{“B”;”H”;””;””}
➥ TEXTJOIN[“”, TRUE, IF[ISERROR[MID[C5, SEQUENCE[LEN[C5]], 1] *1], MID[C5, SEQUENCE[LEN[C5]], 1], “”]]
The final array will be passed over to the TEXTJOIN function, so it concatenates the text characters and outputs the result as-
{BH}
Method 7: Insert User-defined Function to Remove Numbers from a Cell in Excel
Case-1: Remove Numbers from a Cell
In this method, we will create a user-defined function named “RemNumb” using Excel VBA. Then we can apply it to remove numbers from a cell in excel. Follow the steps below.
Step 1:
➤ Right-click on the sheet title.
➤ Select View Code from the context menu.
Soon after, a VBA window will appear.
Step 2:
➤ Later, write the codes given below:
Option Explicit
Function RemNumb[Text As String] As String
With CreateObject["VBScript.RegExp"]
.Global = True
.Pattern = "[0-9]"
RemNumb = .Replace[Text, ""]
End With
End Function➤ Then press the Play icon to run the codes.
Now our function is ready to use.
Step 3:
➤ In Cell D5 type-
➤ Hit the Enter button to get the result.
Step 4:
➤ Finally, drag the Fill Handle icon to copy the formula.
Case-2: Split Numbers and Text into Separate Columns
In our last method, we’ll again use Excel VBA to make a new user-defined function named “SplitTextOrNumb” to split numbers and text into separate columns.
Step 1:
➤ Like the previous method open the VBA window and write the formula-
Option Explicit
Function SplitTextOrNumb[str As String, is_remove_text As Boolean] As String
With CreateObject["VBScript.RegExp"]
.Global = True
If True = is_remove_text Then
.Pattern = "[^0-9]"
Else
.Pattern = "[0-9]"
End If
SplitTextOrNumb = .Replace[str, ""]
End With
End Function➤ Then click Run and a Macro will open up.
Step 2:
➤ Give a macro name and press the Run tab again.
Step 3:
➤ Now our function is ready to apply. To remove text characters now write the formula in Cell D5–
To delete numeric characters:
Step 3:
➤ Finally, just press the Enter button and use the Fill Handle tool to copy the formula.
Conclusion
I hope all of the methods described above will be good enough to remove numbers from a cell in excel. Feel free to ask any questions in the comment section and please give me feedback.
Related Articles
- How to Remove Macros from Excel [5 Suitable Ways]
- Remove Leading Zeros in Excel [7 Easy Ways + VBA]
- How to Remove Duplicate Rows in Excel Table
- Remove Formatting in Excel Without Removing Contents