Could not think of a way to do it w/o converting each cell. Assuming none of
the cells are empty or blank, one way:

=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

Or if there is no data where the last 4 characters are numeric in the cells
between D7 and K7:
=MAX(IF(ISNUMBER(--RIGHT(D7:K7,4)), --RIGHT(D7:K7,4)))
array entered


Is your project dependent on the cells containing the text "st " (since all
of the data is in this format it seems to me the "st " is more for
presentation and wouldn't be absolutely necessary for your worksheet formulae
to work). Is it an option to use a custom number format of
"st "#

That way, your data is presented as "st 1234", but the cell will actually
contain the number 1234. Then you can dispense w/ having to pull out the
number part and just use the MAX function normally. You could use
Find/Replace to replace
st<space
replace with = <nothing-leave it empty

to get rid of the text "st " in your data and convert it to numbers. You
would have to be careful the Find/Replace doesn't affect other data on the
worksheet you do not intend to convert (perhaps copy the data you want
converted to a sheet by itself-do the Find/Replace and then copy it back to
it's original location).

If you do try it, be sure to backup.



"Corey" wrote:

Thanks for the reply JMB.
Is it any easier to get the MAX of these cells:
B7=MAX(D7,G7,I7,K7) witht he same "st xxxx" format ?


"JMB" wrote in message
...
Array formulae cannot use entire columns (for versions prior to XL2007).
Also, I get errors if any of the cells in the target range are empty or
contain data where the last 4 characters are non-numeric. Also, the
formula
cannot go into cell B1 since that cell is part of the formula's argument
(creating a circular reference).

Be aware that any data that ends w/4 numbers will be included in this
formula, not just data that begins w/ "st ".

This seemed to work okay on my machine, change range as needed.
=MAX(IF(ISNUMBER(--RIGHT(A1:I100,4)),--RIGHT(A1:I100,4)))


"Corey" wrote:

I get a #NUM! Value for some reason with all options here ?




"RaceEend" (rot13) wrote in message
...
Corey wrote:

I need to find the Max Value in a Worksheet,
But the Value will always be in a "st 1234" format.
That is:
"st"+ Space then 4 digits.
I want to find the MAX Numerical value in the entire sheet, and
place this value in B1.
How can I do this ?

Something like :
B1= MAX(sheet!A:I(Right(4)) ???

Corey....

Corey,

B1 = MAX(--RIGHT(Sheet!A:I,4))
Entered as a matrix formula (= CTRL + SHIFT + ENTER)


--