How to Remove Numbers From Alphanumeric String in Excel

We have learned how to strip numeric values from a cell in excel 2016 and older. The formulas we used were a little bit complex but now Excel 2019 and 365 are in the game with new toys, I mean functions. 

Excel 2019 and 365 introduce some new functions (TEXTJOIN and SEQUENCE) that can ease the task of removing numeric characters and retrieve only non numeric values in a new cell. We will use formulas that can help us in doing so, more conveniently. 

Generic Formula

Jumbled_text: This is the source text from which you want to extract all numeric values.

NumChars: This is the total number of characters you want to process. The jumbled_text should not have more characters than this number (chars and numeric combined).

Let’s see an example to make things clear.

Example: Remove Numeric Characters and Extract all Alphabets and Non Numeric Characters.

So here we have some alphanumeric text. This text contains some numbers and some non numeric characters. I need to get rid of numeric characters and get alphabets and other character values only, in the D column.

I don't expect the total number of characters in jumbled text to be more than 100. So the value of NumChars is 100 here. You can increase or decrease this number if you need to.

Apply the above generic formula here to strip out the numeric characters.

When you hit the enter button, you get all the numeric characters removed and only alphabetical values are remaining. Drag down this formula to remove numbers from string from all the cells in column B.

How does it work?

First let's see how this formula is solved step by step.

For the ease of reading I have not written the whole array. I used dots (...) to indicate long arrays.

As you can see, the formula starts solving from the inside. At first the SEQUENCE function is solved. Since we have passed 100 as the number of characters. It returns an array of numbers starting from 1 to 100.

This array is served to the MID Function as the starting number. The mid function goes to each index in string and splits each character in an array. You can see that in step 3. I have not shown the whole array as it will take too much space. {"T";"h";"i";"s";" ","1"..."";""}

Next we add 0 to each character. In excel if you try to add number to non numeric characters, it results into #VALUE! Error. So we get an array of numbers and #VALUE! Errors. {#VALUE!;#VALUE!;#VALUE!;#VALUE!;1…;#VALUE!}

This array is served to the ISERROR function. As you know, the ISERROR function returns TRUE for errors and FALSE for non error values. Hence we get an array of TRUE and FALSE. TRUE for non numeric characters and FALSE of numbers. {TRUE;TRUE;TRUE;TRUE;FALSE…;TRUE}.

The same thing happens to TRUE section statements  of IF (MID(B3,SEQUENCE(100),1)). It returns an array of all characters of alphanumeric string in B3.

Now for each TRUE statement, IF function returns the corresponding character from the array of the true section. For FALSE, IF returns blank (""). Now you will have an array that does not contain any numeric character. {"T";"h";"i";"s";""....;""}.

Finally, this array is served by the TEXTJOIN function. This function joins given texts with each other, ignoring the blank values, with a given delimiter. Hence we get a string that does not contain any numeric characters. This is the number one website.

Enhancing the Formula

The above formula uses a hard coded number for processing the number of characters (we took 100). But you may want it to be dynamic. In that case, you guessed it right, you can use the LEN function. It will take the exact number of characters for processing. So the formula will be.

Here, the LEN function will automatically detect the exact number characters in the alphanumeric string. This will relieve from the burden of determining the maximum number of characters.

Alternative of SEQUENCE function

If you do not want to use the SEQUENCE function then you can use a combination of ROW and INDIRECT function  for generating sequential numbers.

The INDIRECT will convert the text ("1:100") into actual range and then the ROW function will list all row numbers from 1 to 100. (100 is just an example. It can be any number).

So yeah guys, this is how you can rip off the non numeric characters from an alphanumeric string in excel. I hope I was explanatory enough and this article helped you. If you have any questions regarding this topic or any other excel/VBA topic. Till then keep Excelling.

Related Articles:

How to Remove Non Numeric Characters From Cells in Excel 2019: To remove non numeric characters from an alphanumeric string in Excel, we use the new TEXTJOIN function. Strip of non numeric characters from a string can help us clean up our data for better data analysis. So here's how you do it

Split Numbers and Text from String in Excel 2016 and Older: When we didn't have TEXTJOIN function we used LEFT and RIGHT functions with some other functions to split numeric and nonnumeric characters from a string. 

Extract Text From A String In Excel Using Excel's LEFT And RIGHT Function: To remove text in excel from string we can use excel's LEFT and RIGHT function. These functions help us chop strings dynamically.\

Remove leading and trailing spaces from text in Excel: Leading and trailing spaces are hard to recognize visually and can mess up your data. Stripping these characters from the string is a basic and most important task in data cleaning. Here's how you can do it easily in Excel.

Remove Characters From Right: To remove characters from the right of a string in Excel, we use the LEFT function. Yes, the LEFT function. The LEFT function retains the given number of characters from LEFT and removes everything from its right.

Remove unwanted characters in Excel: To remove unwanted characters from a string in Excel, we use the SUBSTITUTE function. The SUBSTITUTE function replaces the given characters with another given character and produces a new altered string.

How to Remove Text in Excel Starting From a Position in Excel: To remove text from a starting position in a string, we use the REPLACE function of Excel. This function help us determine the starting position and number of characters to strip.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.