Identify If Cell Contains Alphanumeric Characters

[Nevermore]

Hi,

1. I am trying to find if a cell contains alphanumeric characters or not by using the inbuilt excel function.
2. Is there a way where I can extract only numbers from a cell that contains alphanumeric characters.

Below is an example :

12354-k

Wind1235

<tbody>
</tbody>

 

[yesterdays]

Try this

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,ROW(INDIRECT("1:"&LEN(A1)))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

 

[Nevermore]

@yesterdays

Your formula is awesome, Thanks a ton.
Could you please help me to understand the formula.

 

[vlady]

i use this one to extract numbers.

[COLOR=#333333]=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))[/COLOR]

 

[yesterdays]

Basically , it split the given text to an array with each value contain each digit of given text by MID
Say : A21 become {A;2;1}

then check each value if it is number by ISNUMBER
{A;2;1} now become {FALSE;TRUE;TRUE}


take each number out by multiply the array with it self :
{A;2;1}*{FALSE;TRUE;TRUE}={0;2;1}

multiply them with associated place in number
{0*10^2;2*10^1;1*10^0}

then sum them up by SUMPRODUCT
0+20+1=21

Since I'm not English's native
This is the best I could described it

 

[Peter_SSs]

i use this one to extract numbers.

[COLOR=#333333]=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))[/COLOR]

I think that is designed for cases where there is only a single block of digits in the given text and that is certainly the case with the two samples given in this thread. In general though if there are multiple blocks of digits this will extract the first block, but not consistently - see example 2 below.

However, even with a single block of digits, I would be a little wary using this formula.

With this formula in B1 ..

1. In A1, enter a23b
The correct result occurs in B1. But now insert 2 new rows above row 1.
The result (now in B3) becomes incorrect (#N/A)


If the original text might contain multiple blocks of digits ..

2a. In A1, enter x21e3

2b. In A1, enter a1dec23

In each case an incorrect result occurs.

Try this

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,ROW(INDIRECT("1:"&LEN(A1)))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

Could that red part of the formula be replaced as follows?

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

 

[yesterdays]

Could that red part of the formula be replaced as follows?

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

Oh yes, yes it is