We have placed some **Product Names** and their **IDs** in the dataset. We want to remove the numbers from the **Product IDs**.

**Method 1 – Using Find & Replace with Wildcards to Remove Numbers from a Cell in Excel**

We have some numbers in parentheses in the Products Names column. We will remove these numbers.

**Steps:**

- Select the data range
**B5:B11**. - Press
**Ctrl + H**to open**Find & Replace**command. - Type
**(*)**in the**Find what**box and keep the**Replace with**box empty. - Press
**Replace All**.

- Close the dialog to see the results.

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

**Method 2 – Applying the Find & Select Tool to Delete Numbers from a Cell**

There are two cells in the Product IDs column which contain numbers only. We’ll remove the numbers from the **ID **cells.

**Steps:**

- Select the data range
**C5:C11**. - Go to the
**Home**tab, select**Find & Select,**and choose**Go to Special** - A dialog box will open up.

- Mark only
**Numbers**from the**Constants**options. - Press
**OK**.

- Only the numbers are highlighted.

- Press the
**Delete**button on your keyboard.

**Method 3**** – ****Using Flash Fill to Remove Numbers from a Cell in Excel**

**Steps:**

- Type only the text (not the digits) of the first cell to a new column adjacent to it.
- Hit the
**Enter**button.

- Select
**Cell D5**. - Go to
**Data,**then to**Data Tools**, and select**Flash Fill**.

- The numbers are removed.

**Method 4 – Inserting the SUBSTITUTE Function to Remove Numbers from a Cell in Excel**

**Steps:**

- Use 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,"")`

- Hit Enter.

**Double-click**the**Fill Handle**icon, and the formula will be copied down automatically.

- The numbers are removed from the cells.

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

**Steps:**

- Use this 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.

- Drag the
**Fill Handle**icon down 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**

**Steps:**

- In
**Cell D5,**insert the following 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.

- 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 a User-defined Function in Excel VBA **

**Case 1 – Remove Numbers from a Cell**

**Steps:**

**Right-click**on the sheet title.- Select
**View Code**from the**context menu**. - A
**VBA**window will appear.

- Insert the following code in the window:

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

- Press the
**Play icon**to run the macro.

- In
**Cell D5,**insert:

`=RemNumb(C5)`

- Hit the
**Enter**button to get the result.

- Drag the
**Fill Handle**icon to copy the formula.

**Case 2 – Split Numbers and Text into Separate Columns**

**Steps:**

- Open the
**VBA**window and insert the following code:

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

- Click
**Run**and a**Macro**will open up.

- Select the macro and press
**Run**.

- Insert the following formula in
**Cell D5:**

`=SplitTextOrNumb(C5,1)`

- To delete numeric characters:

`=SplitTextOrNumb(C5,0)`

- Press the
**Enter**button and use the**Fill Handle**tool to copy the formula.

**Download the Practice Workbook**

**Related Articles**

- How to Remove Compatibility Mode in Excel
- How to Remove 0 from Excel
- How to Remove Drop Down Arrow in Excel
- How to Remove HTML Tags from Text in Excel

**<< Go Back To Data Cleaning in Excel | Learn Excel**

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