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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")

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

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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

=--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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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})),"")