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

C1: =LARGE(INDEX(--MID(A$1,FIND("|",SUBSTITUTE(","&A$1&",",",","|",ROW(INDIRECT("1:"&LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+1)))),
FIND("|",SUBSTITUTE(","&A$1&",",",","|",ROW(INDIRECT("2:"&LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+2))))
-FIND("|",SUBSTITUTE(","&A$1&",",",","|",ROW(INDIRECT("1:"&LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+1))))-1),0),B1)

(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:

C1: =IFERROR(LARGE(INDEX(--TRIM(MID(SUBSTITUTE(","&$A$1,",",REPT(" ",LEN($A$1))),LEN(A$1)*ROW(INDIRECT("1:"&LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+1))
,LEN(A$1))),0),B1),"n/a")

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:

C1: =IFERROR(--TRIM(MID(SUBSTITUTE(","&$A$1,",",REPT(" ",LEN($A$1))),LEN(A$1)*B1,LEN(A$1))),"n/a")

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

Does that help?

Your title suggests Max not Nth.

VBA UDFs could be

Function GetMax(rng As Range)
    Dim arrNos As Variant
    Dim n As Long, TempMax As Double
    
    arrNos = Split(rng, ",")
    For n = 0 To UBound(arrNos)
        If arrNos(n) > TempMax Then TempMax = arrNos(n)
    Next
    GetMax = TempMax
End Function

Enter in Excel

=getmax(A1)

Function GetNth(rng As Range, nth As Long)
    Dim arrNos As Variant
    
    arrNos = Split(rng, ",")
    GetNth = arrNos(nth - 1) * 1
End Function

Enter in Excel

=GetNth(A1,3)

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

=IF(A1="","",GetMax(A1))