At times you may want to remove numbers from text in a dataset in Excel. This tutorial shows you four techniques that you can use to remove numbers from text in Excel.
Methods For Removing Numbers From Text in Excel
Method 1: Use Excel’s Flash Fill Feature
In this method, we use Excel’s Flash Fill feature to remove numbers from text in our example dataset. This feature fills data automatically when it detects a pattern in the data.
Suppose we have the following dataset of Intern IDs. We want to use Excel’s Flash Fill feature to remove numbers from the Intern IDs in column A so that we remain with department codes in column B.
We use the following steps:
- Select cell B2 and enter the value in cell A2 minus the numbers:
- Select cell B3 and click Data >> Data Tools >> Flash Fill.
The numbers are removed from the Intern ID text strings in column A and we are left with department codes in column B.
Note: We can also apply the Flash Fill feature by pressing Ctrl + E. First, enter the first value in cell B2 then select the range B2:B9.
Then press Ctrl +E.
Method 2: Use a Formula
In this method, we use a formula that combines the SEQUENCE, FILTER, and CONCAT functions to remove numbers from text in our dataset.
Suppose we have the following dataset of Intern IDs. We want to use a formula to remove numbers from the Intern IDs in column A so that we remain with department codes in column B.
We use the following steps:
- Select cell B2 and enter the following formula:
|
1 |
=CONCAT(LET(textarray,MID(A2,SEQUENCE(LEN(A2)),1),FILTER(textarray,NOT(ISNUMBER(textarray*1))))) |
- Double-click or drag down the fill handle to copy the formula down the column.
Explanation of the formula
|
1 |
=CONCAT(LET(textarray,MID(A2,SEQUENCE(LEN(A2)),1),FILTER(textarray,NOT(ISNUMBER(textarray*1))))) |
- MID(A2,SEQUENCE(LEN(A2)),1) This part of the formula splits the constituent parts of the text string in cell A2 into different rows:
- LET(textarray,MID(A2,SEQUENCE(LEN(A2)),1),FILTER(textarray,NOT(ISNUMBER(textarray*1)))) In this part of the formula the LET function stores the constituent parts of the text string in cell A2 in a variable called textarray. The FILTER function filters numbers from the values in the textarray variable.
- =CONCAT(LET(textarray,MID(A2,SEQUENCE(LEN(A2)),1),FILTER(textarray,NOT(ISNUMBER(textarray*1))))) The CONCAT function concatenates the values in the textarray variable into one string and stores it in cell B2.
Method 3: Use Power Query
In this method, we use Power Query to remove numbers from text in our dataset.
Suppose we have the following dataset of Intern IDs. We want to use Power Query to remove numbers from the Intern IDs in column A so that we remain with department codes.
We use the steps below:
- Select any cell in the dataset and press Ctrl + T to open the Create Table dialog box:
- Check the My table has headers checkbox and click OK.
The data range is converted into an Excel table.
- Select any cell in the table and click Data >> Get & Transform Data >> From Table/Range.
The table is loaded onto the Power Query Editor window.
- Select the header of the table in the Power Query Editor and click Home >> Transform >> Split Column >> By Non-Digit to Digit.
Note: The Non-Digit to Digit option splits the values in the selected column based on transitions from a non-digit to a digit character.
- Select the second column and click Home >> Manage Columns >> Remove Columns to remove it.
- Double-click the header of the table and rename it Department Code.
- Click Home >> Close >> Close & Load.
This inserts the table in a new worksheet in the workbook.
Method 4: Use a User-Defined Function
If our work involves removing numbers from text in Excel regularly, creating a User-Defined Function tailored to the task can save us time and effort.
Suppose we have the following dataset of Intern IDs. We want to create a User-Defined Function that we can use to remove numbers from the Intern IDs in column A so that we remain with only department codes in column B.
We use the following steps:
- Press Alt + F11 to open the Visual Basic Editor.
- Click Insert >> Module.
- Type the following function procedure in the module.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Function EXTRACTTEXT(CellRef As String) Dim stringLength As Integer Dim i As Integer Dim Result As String stringLength = Len(CellRef) For i = 1 To stringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) End If Next i EXTRACTTEXT = Result End Function |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Press Alt + F11 to switch back to the active worksheet that contains the dataset.
- Select cell B2 and type in the following formula:
|
1 |
=EXTRACTTEXT(A2) |
Note: As you begin to type in the User-Defined Function, see that the function is recognized by Excel IntelliSense. You can enter it by pressing the tab key as you would any built-in function:
- Press the Enter key or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
Explanation of the User-Defined Function
- The EXTRACTTEXT function accepts a cell reference as an argument. The cell reference is of String data type.
- The function has two variables of Integer data type and one variable of String data type.
- The length of the string passed to the function is assigned to the stringLength variable.
- The For To Next and If Not Then constructs loop through the string in the cell passed to the function. The non-numeric characters of the string are accumulated in the Result variable. The accumulated characters in the Result variable are then assigned to the EXTRACTTEXT function variable. These are the characters that are returned by the custom function.
Conclusion
Sometimes we may need to remove numbers from text in Excel. This tutorial has shown four methods that we can use to remove numbers from text in Excel. We can use the Flash Fill feature, use a formula, use Power Query, and use a custom function.