Excel Formula for Extracting Text from Alphanumeric Value

This tutorial explains how to extract text from alphanumeric value using Excel formula.

Solution : Suppose alphanumeric value is placed in cell A2.

Solution 1 : Remove Numbers

The following formula is designed to keep only alphabetic characters (both uppercase and lowercase) from the text in cell A2. It removes all numbers from the text.

Press CTRL + SHIFT + ENTER to confirm the following formula as it's an array formula.

=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) + 0), IF(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)=" ", " ", ""), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)))

Solution 2 : Remove Both Numbers and Special Characters

This formula not only removes numbers but also special characters in Excel and returns only text. Make sure to hit CTRL + SHIFT + ENTER to enter the following formula instead of just ENTER. It assumes the text is in cell A2.

=TEXTJOIN("", TRUE, IF((CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) >= 65) * (CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) <= 90) + (CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) >= 97) * (CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) <= 122) + (CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) = 32), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

How it works?

The above Excel formula keeps only uppercase letters, lowercase letters and a space. The formula checks if the ASCII code of each character lies within the ranges for uppercase letters (65-90), lowercase letters (97-122) or equal to the code for a space (32). Then the TEXTJOIN function concatenates all the characters into a single string.

Solution for Excel Version Prior to 2016

For Excel versions older than Excel 2016, we can use the SUBSTITUTE function to extract text from the alphanumeric value. We can't use the above formulas as the TEXTJOIN function was introduced in Excel 2016.

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(+SUBSTITUTE(A2,"0",""),"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9","")

The SUBSTITUTE function replaces a set of characters with another. The above formula replaces 0 through 9 from the text in cell A2 with blank (space). Hence, all the numeric values are removed from the alphanumeric string in Excel.

Related Posts

Excel Tutorials : Top 100 Excel Tutorials

Spread the Word!
Share Share Tweet

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn