How to Remove Numbers from a Cell in Excel (7 Effective Ways)

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.

Find & Replace with Wildcards to Remove Numbers from a Cell in Excel

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.

Find & Select Tool to Delete Numbers from a Cell in Excel

Step 2:

➤ Mark only Numbers from the Constants option.

➤ Press OK.

Find & Select Tool to Delete Numbers from a Cell in Excel

Now, look that the cells, only the numbers are highlighted.

Find & Select Tool to Delete Numbers from a Cell in Excel

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.

Flash Fill to Remove Numbers from a Cell

Step 2:

➤ Select Cell D5.

➤ Now go to Data > Data Tools > Flash Fill.

Flash Fill to Remove Numbers from a Cell

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.

SUBSTITUTE Function to Remove Numbers from a Cell in Excel

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:


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.

TEXTJOIN, MID, ROW, LEN, and INDIRECT Functions to Erase Numbers from a Cell in Excel

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.

TEXTJOIN, IF, ISERR, SEQUENCE, LEN, and MID Functions Together to Delete Numbers from a Cell in Excel

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.

User Defined Function to Remove Numbers from a Cell in Excel

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.

User Defined Function to Remove Numbers from a Cell in Excel

Now our function is ready to use.

Step 3:

➤ In Cell D5 type-

=RemNumb(C5)

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

Split Numbers And Text into Separate Columns

Step 2:

➤ Give a macro name and press the Run tab again.

Split Numbers And Text into Separate Columns

Step 3:

➤ Now our function is ready to apply. To remove text characters now write the formula in Cell D5

=SplitTextOrNumb(C5,1)

Split Numbers And Text into Separate Columns

To delete numeric characters:

=SplitTextOrNumb(C5,0)

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo