# 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

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.``

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

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.

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

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

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

Excelent Thank You

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1