Excel Formula for Extracting Text from Alphanumeric Value

Deepanshu Bhalla 7 Comments

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

Excel Formula for Extracting Text from Alphanumeric Value

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
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

Post Comment 7 Responses to "Excel Formula for Extracting Text from Alphanumeric Value"
  1. Yo need to update the formula as it does not remove number zero from text

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

    ReplyDelete
  2. Thanks a ton Deepanshu & Nitish Walia

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

    ReplyDelete
    Replies
    1. I have added the multiple ways to handle this issue. Thanks!

      Delete
Next → ← Prev