CODEWITHSUNDEEP
excelexcel-formula
Danny
Danny

Reputation: 100

How can I get the minimum value from a comma separated cell containing number

I found this formula which I can get the max value form a cell containing comma separated numbers. How can I get the min value using a similar approach?

=MATCH(1000,INDEX(FIND(","&ROW(INDIRECT("1:999"))&",",","&D2&","),0))

Cell content '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'

Upvotes: 0

Views: 1227

Answers (3)

JvdV
JvdV

Reputation: 75950

I'd also go with FILTERXML. But if your string always follows this pattern it seems like the smallest number is always found on the left before the first comma which you could make perfect use of!

=--LEFT(A1,FIND(",",A1&",")-1)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

If one has FILTERXML:

=MIN(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"))

For the MAX, just replace the MIN with MAX.

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54883

You can try this:

=MIN(0+MID(SUBSTITUTE(A1, ",", REPT(" ",255)), 255*(ROW(INDIRECT("1:"&(1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))-1)+1,255))

Upvotes: 1

Related Questions