Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
ok, I need to write a formula that will check a cell with several words and
numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
On Thu, 23 Jul 2009 12:54:02 -0700, Tech_Wolf
wrote: ok, I need to write a formula that will check a cell with several words and numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? Provide some examples of data and desired output, in a way that demonstrates the types of numbers and formats you might be dealing with. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
If your text is in A1, maybe try this *array* formula in B1:
=MAX(--(TEXT(MID(A1,COLUMN(A:IV),{1;2;3;4;5;6;7;8;9;10}), "0;0;0;\0")&" 0/1")) executed using with CTRL+SHIFT+ENTER not just enter. This extracts a positive whole number up to 10 digits and returns 0 if none is found. (You could add a few more numbers to get up to 15 digit numbers which is the limit of Excel's precision but for larger numbers you'd need to use a text UDF.) "Tech_Wolf" wrote: ok, I need to write a formula that will check a cell with several words and numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
Ok, in cell E3-Ex (as many cells as user needs), there will be imput along
these lines: Acidosis, severe [5], Anemia, moderate [3]. I need to somehow automatically extract the highest numerical value from that imput and place it into cell G3-Gx, where from it will be operated on by some math formulas. "Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 12:54:02 -0700, Tech_Wolf wrote: ok, I need to write a formula that will check a cell with several words and numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? Provide some examples of data and desired output, in a way that demonstrates the types of numbers and formats you might be dealing with. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
On Fri, 24 Jul 2009 05:19:01 -0700, Tech_Wolf
wrote: Ok, in cell E3-Ex (as many cells as user needs), there will be imput along these lines: Acidosis, severe [5], Anemia, moderate [3]. I need to somehow automatically extract the highest numerical value from that imput and place it into cell G3-Gx, where from it will be operated on by some math formulas. Since these numbers are all relatively small integers, Lori's formula will work well. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
If you're only looking for numbers 1-5 enclosed in [ ], maybe try in G3:
=MATCH(4^8,FIND("["&{1,2,3,4,5}&"]",E3)) then fill across. "Tech_Wolf" wrote: Ok, in cell E3-Ex (as many cells as user needs), there will be imput along these lines: Acidosis, severe [5], Anemia, moderate [3]. I need to somehow automatically extract the highest numerical value from that imput and place it into cell G3-Gx, where from it will be operated on by some math formulas. "Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 12:54:02 -0700, Tech_Wolf wrote: ok, I need to write a formula that will check a cell with several words and numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? Provide some examples of data and desired output, in a way that demonstrates the types of numbers and formats you might be dealing with. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
Lori wrote:
If your text is in A1, maybe try this *array* formula in B1: =MAX(--(TEXT(MID(A1,COLUMN(A:IV),{1;2;3;4;5;6;7;8;9;10}), "0;0;0;\0")&" 0/1")) executed using with CTRL+SHIFT+ENTER not just enter. This extracts a positive whole number up to 10 digits and returns 0 if none is found. (You could add a few more numbers to get up to 15 digit numbers which is the limit of Excel's precision but for larger numbers you'd need to use a text UDF.) "Tech_Wolf" wrote: ok, I need to write a formula that will check a cell with several words and numbers in it, take the highest number is that cell, and display it in a seperate cell as a numerical value, so other formulas can use it. How can I accomplish this? I knew I marked this message for a reason. This is a very cool technique Lori! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract highest number
Glad this was helpful too. Can't remember where/who the idea came from.
&" 0/1" is misplaced though, it should actually be inside the TEXT function just before the last comma. Appending a fractional part of zero ensures all non-integer values in the text are ignored (dates, times, exponentials, etc.) &"e0" would include decimals and thousand separators in the result too. "smartin" wrote: I knew I marked this message for a reason. This is a very cool technique Lori! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Screen through to extract highest education qualification | Excel Discussion (Misc queries) | |||
Add a number '1' next to highest number in column | Excel Discussion (Misc queries) | |||
highest number if criteria | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
Determining the highest number in a row | Excel Discussion (Misc queries) |