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
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
Last edited by MPLM; 12-12-2018 at 02:28 PM.
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.
Last edited by Bernie Deitrick; 12-12-2018 at 03:44 PM.
Bernie Deitrick
Excel MVP 2000-2010
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
Array-enter the formula using Ctrl-Shift-Enter. If you do that correctly, Excel will show the formula within { } on the formula bar.
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
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
thank you very much Glenn and Bernie the formula
works perfectly.
Marco
Excelent Thank You
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks