How can one remove text and leave numbers in Microsoft Excel or vice versa

In this respective tutorial will be learning how we can easily separate out the text from numbers in Microsoft Excel by making use of the native formulas as well as the custom functions. We will also be learning how we can easily split text as well as the particular numbers into two separate columns.

Let us now imagine that: we receive the raw data for the purpose of analyzing and finding that the respective numbers are mixed with text in one column as well. While in various situations, it will be more convenient to have them in the separate columns to examine it closely.

Besides all these, if in case we are working with the homogeneous data, then in that case we can effectively make use of the LEFT, RIGHT, as well as the MID functions in order to extract the same number of characters from the same position respectively.

But that could be an ideal scenario for laboratory test, moreover in the case of real life, we are likely to deal with dissimilar data in which the numbers usually come before the selected text, after the selected text, or in between of the text as well:

Now the examples below will provide effective solutions for this case as well.

1. How can we easily remove the text from the given numbers?
2. How to delete numbers and keep text in the Excel sheet?
3. How can one separate text from the numbers into two columns in Microsoft Excel?
4. How can one use special tools to remove the numbers or text in Microsoft Excel?

How can we easily remove the text from the given numbers in Microsoft Excel?

This particular solution will work efficiently in Microsoft Excel 365, Microsoft Excel 2021, and Microsoft Excel 2019 respectively.

We all know, that the respective Microsoft Excel version 2019 has basically introduced out a new functions which are not readily available in earlier versions so in this we are making use of one of such functions, that is none other than the: TEXTJOIN Function; this function is used for the purpose of stripping out the text characters from a respective cell containing numbers.

And the generic formula that can be used for the TEXTJOIN Function in Microsoft Excel is as follows:

Moreover, in Microsoft Excel 365 and 2021 versions, this one will also be working out effectively:

And to the very first sight, the above used formulas may look bit intimidating, but they also work very well:

• For example: If in case we want to remove out the respective text from numbers in cell A2, then in that case we effectively need to enter one of the below formulas in cell B2 and then copy it down to as many cells as required by us.

In Microsoft Excel 365 - 2019, we can make use of the below-mentioned formula:

In Microsoft Excel version 2019, it must be entered as an array of formulas with the help of the shortcut key: Ctrl + Shift + Enter. And in the dynamic array Excel, the formula will be working as normal formulas which are completed by making use of the Enter key present on our Keyboard as well.

In Microsoft Excel 365 and 2021, we can make use of the below-mentioned formula as well:

And as a result, all the respective text characters will get removed from a cell, and numbers are kept respectively:

Let us now see the working of the above-mentioned formula:

Now for a better understanding of the above used logic, let us start investigating the formula from the inside also to get better insight of it:

We can make use of the ROW(INDIRECT("1:"&LEN(String))) or the SEQUENCE(LEN(String)) for the purpose of create a sequence of a number which are corresponding to the total number of the characters in the given source string after that will feed those sequential numbers to the MID function considering it as the starting numbers. As in the case of cell B2, this part of the formula may looks as follows:

And in the above formula, the MID function is basically responsible for the purpose of extracting out each character from cell A2, beginning with the very first one and then returning them in the form of an array:

After that the received array will be then multiplied by number 1. The Numeric values survive with no change while multiplying a non-numeric character will be outcome as a #VALUE! Error:

Whereas, the respective IFERROR function will be responsible for handling these errors as well as replacing them with empty strings:

And now, this final array is served to the TEXTJOIN function, which will be concatenating the non-empty values in the given array by making use of an empty string ("") for the delimiter as well:

Important Tip: It should be kept in mind that a particular solution will be existing for Microsoft Excel version 2016 - 2007, but the formula is much more complex in comparison to others as well.

How can we make use of the Custom function to remove text from numbers in Microsoft Excel?

This solution will effectively work for all the Excel versions without failure.

Suppose we use an older version of Microsoft Excel or need help remembering the above formulas. In that case, we can create our own function with simpler syntax and a user-friendly name like RemoveText.

Besides all these, the respective user-defined function (UDF) can be easily written in two simpler ways that are as follows:

VBA (Virtual Basic for Application) code 1:

Here, in this we will be looking at each character in the source string individually and then we will be checking out whether it is a numeric or not. And if in case it is a number, then we will be adding the character to the resulting String effectively:

VBA (Virtual Basic for Application) code 2:

The code will effectively create an object in order to process a regular expression. By making use of the RegExp, we will be removing all characters other than digits ranging from 0-9 from the source string respectively:

And on the small worksheets, both the codes will perform equally well, whereas on the large worksheets in which the function is called hundreds or thousands of times, code 2 make use of VBScript, as well as RegExp as these work much faster in comparison to others.

Whichever approach we are opting, from the end-user perspective, the particular function needs for the purpose of deleting all the text and then leaving out the numbers is as simple as this:

And for an instance, if we want to remove out a non-numeric character from cell A2, then in that scenario we will be making use of the formula in cell B2 which are as follows:

Now after that we will be copying it down to the column, and we will get this outcome:

Note. It was well noted that both the native formulas as well as the custom function output a numeric string. And in order to turn it into a number, we will multiply the result by 1, add zero, or wrap the formula in the VALUE function.

• For example:

How can one remove numbers from text string in Microsoft Excel?

The solution works well in Microsoft Excel 365, Microsoft Excel 2021, and Microsoft Excel 2019.

And the formulas which can be used for the purpose of remove the numbers from an alphanumeric string are much similar to the ones discussed in the previous example.

For Microsoft Excel version 365 and 2019:

And in Microsoft Excel version 2019, we will be then remembering to make it an array formula by just pressing the shortcut Ctrl + Shift + Enter keys together.

And for the Microsoft Excel 365 and 2021, we can make use of the below-mentioned:

• For example: To strip numbers from a string in cell A2, the formula is:

Or

And as a result, all numbers are removed from a particular cell while the text characters are kept in it.

As we are encountered with the above screenshot, the formula will be stripping out the numeric characters from any position in a given string: in the beginning, and in the End, or the middle.

And in order to get rid of the extra spaces before the text, we will be then wrapping the formula in the TRIM function like this:

How the above formulas work in Microsoft Excel?

It was assumed that this particular formula will also be working just same as explained in the previous example. But the only difference is that from the final array served to the TEXTJOIN function, we are required to remove the numbers and not the text from it and to have it done, we will be using the combination of IF and ISERROR functions.

And the ISERROR function will be then catches the errors and then it will be passes the resulting array of Boolean values to the IF functions:

Besides all this, when the IF function sees TRUE (an error), then in that case it will be inserting out the corresponding text character into the processed array by making use of the other MID function as well. And if in case, the IF function encounters the FALSE (a number), it will be then replacing it with an empty string as well:

This is the final array, passed over to TEXTJOIN to concatenate the respective text characters and output the result.

How to make use of the Custom function to remove numbers from text in Excel?

The best thing about this is that these particular solutions will work effectively for all Excel versions.

We should keep in mind that the formula that we are going to use must be robust and simple, and we will be sharing the code of the user-defined function (UDF) for the purpose of stripping of any numeric character from the selected Excel sheet:

VBA code 1:

VBA (Virtual Basic for Application) code 2:

Now in the case of the Removal of Text function, the second code is much better to be used in large worksheets for the effective optimization of the performance as well:

And once the code is added to our workbook, after that we can easily remove all numeric characters from a cell by just using custom function:

As in our case, the formula in cell B2 is:

And in order to trim the leading spaces, if any, we will nest the custom function inside TRIM:

=TRIM (RemoveNumbers (A2))

How can one Split number and text into separate columns in Microsoft Excel?

And in a particular situation, when we want to separate out the text as well as the numbers into two columns, then in that case we would be making use of the single formula. And for this, we need to merge the code of the RemoveText as well as the RemoveNumbers functions into one function, which is named as SplitTextNumbers, or simply Split:

VBA (Virtual Basic for Application) code 1:

VBA (Virtual Basic for Application) code 2:

And our new custom function would require two arguments:

Where is_remove_text is a Boolean value that will be indicating which characters need to be strip out:

1. TRUE or 1:As it will be removing out the text and keeping the numbers in the sheet.
2. FALSE or 0: It will be subtracting the numbers and keeping the text.

And for our sample dataset, the formulas would take this form as well:

• For the purpose of removing a non-numeric characters:

• For the purpose of deleting the numeric characters:

Important Tip: For the purpose of avoiding a potential problem of leading spaces, we will always be recommending to wrap out the formula, which will be removing the numbers in the TRIM function as well:

How to make use of the special tool for the purpose of removing numbers or text in Excel?

It is for those who do not like making things complicated unnecessarily, we will be showing our own way of removing text as well as the numbers in Excel sheet.

1. Firstly on the Data tab, under in the Text group, we will be clicking on the Remove > Remove Characters respectively.
2. After that on the add-in's pane, we will be selecting out the source range, and will be choosing the Remove character sets option, and pick either of the Text characters or the Numeric characters in the drop-down list s:
3. And then we will be hitting on the Remove and will get the output as well: