A Challenge

For your example with a 2 digit number

=Value(MID(A1,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1)),1000,FIND({0,1,2,3,4,5,6,7,8,9},A1))),2))
entered as an array (control-shift-enter)

It gets messy if you don't know how many digits there are.
"Jazzer" wrote:

>
> Hi,
>
> A little challenge to all of you.
>
> Can you make a worksheet formula (not VBA), that returns the first
> numeric value in a text string? ie from a string "quite many (>45,
> <75)", it would return 45.
>
> - Asser
>
>
> --
> Jazzer
> ------------------------------------------------------------------------
> Jazzer's Profile: http://www.excelforum.com/member.php...fo&userid=4464
> View this thread: http://www.excelforum.com/showthread...hreadid=385560
>
>

bj wrote...
>For your example with a 2 digit number
>
>=Value(MID(A1,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1)),1000,FIND({0,1,2,3,4,5,6,7,8,9},A1))),2))
>entered as an array (control-shift-enter)
>
>It gets messy if you don't know how many digits there are.
....

It doesn't get all that messy. If the numbers could be any nonnegative
integer up to 15 digits in length (so 0 to 999,999,999,999,999), it
could be done using the normal formula

=IF(MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789"))<=LEN(A1),
LOOKUP(1E+300,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},
A1&"0123456789")),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})),"")

bj wrote...
>For your example with a 2 digit number
>
>=Value(MID(A1,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1)),1000,FIND({0,1,2,3,4,5,6,7,8,9},A1))),2))
>entered as an array (control-shift-enter)
>
>It gets messy if you don't know how many digits there are.
....

It doesn't get all that messy. If the numbers could be any nonnegative
integer up to 15 digits in length (so 0 to 999,999,999,999,999), it
could be done using the normal formula

=IF(MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789"))<=LEN(A1),
LOOKUP(1E+300,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},
A1&"0123456789")),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})),"")