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.

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: Using 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.

**Read More:** How to Remove Value in Excel

**Method 2: Applying Excel Find & Select Tool to Delete Numbers from a Cell**

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: Using Flash Fill to Remove Numbers from a Cell in Excel**

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.

**Read More: **How to Remove Dotted Lines in Excel

**Method 4: Inserting 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.

**Method 5: Combine TEXTJOIN, MID, ROW, LEN, and INDIRECT Functions to Delete Numbers**

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}**

**Read More: **How to Remove Outliers in Excel

**Method 6: Apply TEXTJOIN, IF, ISERROR, SEQUENCE, LEN, and MID Functions to Remove Numbers**

We’ll combine another set of functions to do the task now. That’s are the **TEXTJOIN**, **IF**, **ISERROR**, **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 **ISERROR **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: Removing Numbers from a Cell with User-defined Function in Excel VBA **

**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-

`=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.

**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**–

`=SplitTextOrNumb(C5,1)`

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.

**Download Practice Book**

You can download the free Excel template from here and practice on your own.

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

Sometimes you search an obscure Excel issue and some random guy on the internet tells you exactly how to solve your problem. THanks!

Hello,

Im!Hope you are doing well. Thanks for your appreciation.

Regards

ExcelDemy