Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=--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 |
#4
|
|||
|
|||
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})),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH FUNCTION?...challenge | Excel Worksheet Functions | |||
Comparison Challenge | Excel Discussion (Misc queries) | |||
Who is up for a challenge? | Excel Discussion (Misc queries) | |||
Divide Ranks into two teams (mathematical guru challenge) | Excel Discussion (Misc queries) | |||
CHALLENGE! - USING IF MAX MIN AND LOOKUP TOGETHER | Excel Worksheet Functions |