# Hello, I need a formula to extract the numbers from an alphanumerical cell

1. ## Hello, I need a formula to extract the numbers from an alphanumerical cell

The formula should do the following:

ORIGIN --------- EXPECTED
cell B3 -------- cell J3
D11 -------- 11
HEP100 -------- 100
L70 * 7 -------- 70
L70 * 123 ------ 70
PL10 * 250----- 10

I hope you can help.
Thank you.
Marco  Register To Reply

2. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

Array-enter (enter using Ctrl-Shift-Enter) this in J3:

=SUM(MID(0&LEFT(B3,FIND(" ",B3&" ")-1),LARGE(INDEX(ISNUMBER(--MID(LEFT(B3,FIND(" ",B3&" ")-1),ROW(\$1:\$99),1))*ROW(\$1:\$99),),ROW(\$1:\$99))+1,1)*10^ROW(\$1:\$99)/10)

You could also use VBA - copy the function below into a standard codemodule and save your workbook as a macro-enabled .xlsm and use the code like

=FirstNum(B3) ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

Hello Bernie, thanks for the help but the result of the formula is that it returns the last digit. You can help solve the problem.

ORIGIN --------- EXPECTED------RESULT
cell B3 -------- cell J3 -------- cell J3
D11 -------- 11 -------- 1
HEP100 -------- 100 -------- 0
L70 * 7 -------- 70 -------- 7
L70 * 123 ------ 70 -------- 3
PL10* 250 ----- 10 -------- 0

Thank you.
Marco  Register To Reply

4. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

Array-enter the formula using Ctrl-Shift-Enter. If you do that correctly, Excel will show the formula within { } on the formula bar.  Register To Reply

5. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

Here's a non-array formula to do the same thing:

=TRIM(LOOKUP(99^99,--("0"&MID(B3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B3&"0123456789")),ROW(\$1:\$100)))))+0  Register To Reply

6. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

thank you very much Glenn and Bernie the formula
works perfectly.
Marco  Register To Reply

7. ## Re: Hello, I need a formula to extract the numbers from an alphanumerical cell

Excelent Thank You  Register To Reply