A Challenge

[Jazzer]

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

 

[Guest]

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.

 

[N Harkawat]

=--MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,Q,1)),0),SUMPRODUCT(--ISNUMBER(-MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,Q,1)),0),Q))))
array entered (ctrl+shift+enter)

where Q is a named range defined as
=row(indirect("1:"&len(a1)))

 

[Harlan Grove]

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})),"")