Extract highest number

[Tech_Wolf]

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?

 

[Ron Rosenfeld]

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

 

[Lori]

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]

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]

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

 

[Lori]

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.

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.

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

 

[Lori]

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.