Users often ask how to remove numbers from a cell in Excel. This query is most common because it makes them confuse while removing data from the sheet. It happens because sometimes, text and numbers are mixed up and when it comes to deleting one type of data it does now work.
Today, you will get to learn how different methods can help you remove numbers from a cell with easy-to-follow steps.
How to Remove Text and Keep Numbers in Excel Cells?
The method you are going to learn works with Excel 365, 2021, and 2019 only. In Excel 2019, users must have found some unique features introduced that were not present in the earlier versions.
In this method, we are going to use one such function like TEXT JOIN to simply remove the text data from a cell.
The syntax is given below:
TEXTJOIN(“”, TRUE, IFERROR(MID(cell, ROW(INDIRECT( “1:”&LEN(cell))), 1) *1, “”))
You can use the following formula as well for Excel 365 and 2021:
TEXTJOIN(“”, TRUE, IFERROR(MID(cell, SEQUENCE(LEN(cell)), 1) *1, “”))
At first, you may find the above formulas as nerve-racking but they really work. Suppose you need to remove text characters from numbers in A2, put the following formula in B2, and copy it to as many cells as needed.
For Excel 365 and 2019:
=TEXTJOIN(“”, TRUE, IFERROR(MID(A2, ROW(INDIRECT( “1:”&LEN(A2))), 1) *1, “”))
Excel 2019 users must put this formula as an array with CTRL + SHIFT + ENTER command.
For Excel 365 and 2021:
=TEXTJOIN(“”, TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, “”))
It will keep all the numbers while removing the text from the cell.
Let’s have a look at brief explanation of how this formula works in real:
You can make a number order related to the total number of characters in the source string with ROW(INDIRECT(“1:”&LEN(STRING))) or SEQUENCE(LEN(string)).
Later on, you can put these number orders to the MID function as the starting numbers. You can notice in the B2 cell, this part of the formula will look like this:
MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}, 1)
As you can see the MID function is present, it drags out each character from A2 that starts with the first one and then gives an array:
{“2″;”1″;”0″;” “;”S”;”u”;”n”;”s”;”e”;”t”;” “;”R”;”o”;”a”;”d”}
You can see this array multiplies by 1 and number values are present without any change. On the other hand, if there are non-numeric values and you multiplied them, chances are having a #VALUE! Error:
{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
To remove such errors, you may have to use the IFERROR function while replacing them with blank strings:
{2;1;0;””;””;””;””;””;””;””;””;””;””;””;””}
In the above array, TEXT JOIN finally appears that helps in concatenating the non-empty values in the array while using an empty string (“”) for the delimiter:
TEXTJOIN(“”, TRUE, {2;1;0;””;””;””;””;””;””;””;””;””;””;””;””})
Remove Numbers from a Cell in Excel with Find & Replace with Wildcards
In this method, you will learn how the Find & Replace feature with the Wildcards can help you remove numbers from cell in Excel. Here the given dataset contains numbers present within parenthesis in the column of Product Names. Let’s see how to remove numbers from Excel cell:
- Choose a data range from B5:B11.
- Open the Find & Replace command with CTRL + H command.
- In the Find what box put the (*) and leave the Replace with box as it is.
- Now, press Replace All button.
You will instantly notice all the numbers have vanished from the column.
That’s it!
Remove Numbers from a Cell in Excel with Excel Flash Fill Option
The above method is easier than the first one, but you will find this method super easy. In the given dataset, the product IDs are present in which you can see a text and numbers combo. In this method, you will get to know how to remove numbers from text in Excel with the Excel Flash Fill method.
- In a new adjacent column, enter only the text of the first cell.
- Press the ENTER key.
- Select Cell D5.
- Open the Data tab.
- Click on the Data Tools option and then select Flash Fill.
- All the numbers will be removed in the next step.
Isn’t that easy?
Remove Numbers from a Cell in Excel with Find & Select Tool
In this method, the given dataset contains two cells in the Product IDs column that has numbers only. You will see how these numbers will be removed from the IDs with the help of the Find & Select command.
- Choose the data range from C5:C11.
- Open the Home tab.
- Select the Editing group from the menu.
- Click on the Find & Select option and then choose Go to Special option.
- A new dialog box will pop up.
- From the Constants option, you need to check the Numbers only.
- Press OK.
- You will notice the cells with numbers get highlighted.
- Press the Delete button from the keyboard.
- And all the numbers will be removed in a go.
Summary:
In this post, you have learned how easily you can remove numbers from a cell in Excel. This post contains multiple methods to use and each method has its unique importance. You can use any of the above methods. It will also let you know which method suits best to you.
In this post, you’ve got more than one answer on how to remove numbers from Excel cell query. All the above-mentioned methods are worthy enough to use, that’s why you can use any of them.
