Find largest number in string containing multiple numbers

Split the cell using Text to columns, then use the MAX function.

As long as the numbers are each separated by a comma and a space...
A1: 35.328, 32.086, 33.326, 34.479, 34.268, 33.953, 32.497, 31.37, 30.963, 29.252, 29.152, 28.655
B1: n-th largest value to find....3

This regular formula will return the n-th largest value in the text.
EDITED: REPLACE MY ORIGINAL FORMULA (WHICH DID NOT FIND THE LAST NUMERIC VALUE...WITH THIS TWEAKED (AND EQUALLY UGLY) FORMULA

Please Login or Register  to view this content.

(Yeah...I know....ugly as a mud fence, but it works )

In the above example, that formula returns: 34.268 (the 3rd largest value)

If B1: 1
The returned value is: 35.328 (the largest value)

Is that something you can work with?

Try this,

For MAX, Confirmed with CTRL+SHIFT+ENTER

=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1))),(ROW(A$1:A$200)*LEN(A1))-1,LEN(A1))),0))

For xth number

=INDEX(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1))),(ROW(A$1:A$200)*LEN(A1))-1,LEN(A1))),x)

Change x to a number

EDIT:

for the nth LARGEST

=LARGE(IFERROR(--TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1))),(ROW(A$1:A$200)*LEN(A1))-1,LEN(A1))),0),n)


Array Entered.

OK...Here's what's depressing...I've got that approach in my formula stash, but never even checked!
(Really...why do I bother having a stash if I don't even use it)

Anyway...With
A1: 35.328, 32.086, 33.326, 34.479, 34.268, 33.953, 32.497, 31.37, 30.963, 29.252, 29.152, 28.655
B1: the n-th largest value to find

Using the variation I've got stashed, here's a regular (non-array) version:

Please Login or Register  to view this content.

Originally Posted by Snowtoad

Thanks for the efforts guys, but I just meant nth number chronologically, (not ascending or descending but simply listed from left to right)!
Cheers

HAH! That's funny...
Here you go:
With
A1: 35.328, 32.086, 33.326, 34.479, 34.268, 33.953, 32.497, 31.37, 30.963, 29.252, 29.152, 28.655
B1: the n-th value to find...3

This regular formula returns that value:

Please Login or Register  to view this content.

In that example, the formula returns: 33.326 (the 3rd value)

Does that help?

Your title suggests Max not Nth.

VBA UDFs could be

Please Login or Register  to view this content.

Enter in Excel

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Enter in Excel

Please Login or Register  to view this content.

For reference only, best to use native formula when ever possible.

I think you will find that my function "GetMax" for max is a tad more efficient.

If you have blank rows use like so

Please Login or Register  to view this content.