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

**Solution :** Suppose alphanumeric value is placed in cell A2.

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)))

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), ""))

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.

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.

Yo need to update the formula as it does not remove number zero from text

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

I have updated the formula. Thanks!

Delete6+6=

ReplyDeleteThanks a lot

ReplyDeleteThanks a ton Deepanshu & Nitish Walia

ReplyDeleteTHIS is what I need - but I need the numeric characters not alpha. Is there a formula?

ReplyDeleteI have added the multiple ways to handle this issue. Thanks!

Delete