A Challenge
 

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

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

=--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)))


"Jazzer" wrote in
message ...

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;1 4;15})),"")