Remove alpha characters from alphanumeric string

[azdub2]

I need to remove the alpha characters, from an alphanumeric string. An example of what I have, is in column A and the results I want, are in column B.

A	B
1abcd	1
8.2b	8.2
13.12def	13.12
51ef	51

<tbody>
</tbody>

Any help is greatly appreciated.

 

[Joe4]

This link shows formula and VBA options of doing that: https://www.extendoffice.com/docume...emove-letters-from-strings-cells-numbers.html

 

[azdub2]

I have tried what is in that link to no avail. I need the the numbers and decimals ie 8.2 and 13.12. It needs to be a formula.

 

[Joe4]

I am searched high and low, but cannot find any formula only (non-VBA) solutions that leave the decimal point.
I could come up with a VBA solution to leave the decimal point, though that does not sound like what you want.

 

[Rick Rothstein]

I need to remove the alpha characters, from an alphanumeric string. An example of what I have, is in column A and the results I want, are in column B.

A	B
1abcd	1
8.2b	8.2
13.12def	13.12
51ef	51

<tbody>
</tbody>

Any help is greatly appreciated.

Are your numbers always at the beginning of the text like your examples above show?

 

[Jonmo1]

Try

=LOOKUP(9.99999999999999E+307,LEFT(A1,ROW(A$1:A$100))+0)

The 100 in A$1:A$100 needs only be as large as the possible length of string in the cell.

You could do this to vary it..
=LOOKUP(9.99999999999999E+307,LEFT(A1,ROW(A$1:INDEX(A:A,LEN(A1))))+0)

 

[AlKey]

Or

[B]=-LOOKUP(1,-LEFT(A1,COLUMN(A:O)))[/B]

Unknown
	A	B
1	1abcd	1
2	8.2b	8.2
3	13.12def	13.12
4	51ef	51
Sheet5

 

[azdub2]

"=LOOKUP(9.99999999999999E+307,LEFT(A1,ROW(A$1:A$100))+0)"

Thanks Jonmo1, that works perfectly.

 

[AlKey]

Hi azdub2
9.99999999999999E+307 is indeed a very big number. However, in the formula I provided I used 1 as a big number. You may have noticed that minus sign before LEFT function. The minus sign converts positive numbers to to negative thus making 1 larger than any negative number.
There is another minus sign added at the beginning of the formula which will convert negative to positive.

 

[Jonmo1]

That formula then needs to perform the - operation 16 times, once for each of the 15 LEFT functions +1 on the lookup.