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?

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

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?

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

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

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

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!

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!