Q : Mark asked if we can extract text from alphanumeric value using excel formula.

Enter the following formula in cell B2.

Solution : Suppose alphanumeric value is placed in cell A2.

Enter the following formula in cell B2.

**How it works ?**

**The Microsoft Excel SUBSTITUTE function replaces a set of characters with another.**

**Trick :**

**The above formula replaces 1 through 9 with blank value. Hence, all the numeric values are rubbed out from the alphanumeric string.**

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

6+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?

ReplyDeleteDon't you think this is pretty terrible solution? What if you wanted only the characters a-zA-Z, would you suggest burying a2 in substitutes for the entire ascii table excluding the characters you want? This answer is ugly and only applicable to a very specific use case.

ReplyDelete