Min and Max on comma separated values in a cell

[moishier]

I have cells that contain comma separated number values. I need to extract the MIN and MAX numbers from each cell.

How can this be done?

I found this formula:

=MATCH(10,INDEX(FIND(","&ROW(INDIRECT("1:99999"))&",",","&AH13&","),0))

But it only gives me the MAX, not MIN.

I saw some VBA solutions but could not get them to work.

 

[Rick Rothstein]

I have cells that contain comma separated number values.

Are the cells formatted as Text? If they are not, and depending on how large your values are, then you might not have a set of comma separated values, instead you might have a single number with thousands separators showing. So, can you tells us if your cells are formatted as Text? If not, can you show us a representative sample of the smallest set of numbers (least number of digits) you will have?

 

[Rick Rothstein]

found this formula:

=MATCH(10,INDEX(FIND(","&ROW(INDIRECT("1:99999"))&",",","&AH13&","),0))

But it only gives me the MAX, not MIN.

No, it does not give you the MAX - consider this... 123,5555,3333,45,2

 

[Rick Rothstein]

No, it does not give you the MAX - consider this... 123,5555,3333,45,2

But, assuming your cells contain TEXT values and not numeric values, these array-entered** formulas will give you the MAX and MIN...

[table="width: 500"]
[tr]
	[td]=MAX(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300)))

=MIN(0+TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))*300,300)))[/td]
[/tr]
[/table]

**Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[mikerickson]

If you move the +0 inside the MIN, you can eliminate the TRIM

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

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

 

[pawan]

Hi

I have the same problem. Is there a simpler formula to do this? I have some 40K rows on which this min/max has to be found. The machine simply hangs if i drag the formulae over so many rows.

 

[Peter_SSs]

I have some 40K rows on which this min/max has to be found.

Could we have a small set of dummy (but representative) sample data (5-20 rows) using XL2BB including the expected results entered manually?

The machine simply hangs if i drag the formulae over so many rows.

Which formula? There has been quite a few suggested in the thread.

 

[pawan]

Data	Expected Result Min (Non Zero)	Expected Result Max
3897,2561,0,0,0,0,0,0,,,,5767,4479,3221,0,0,0,0,0,295,295,,,,,3677,,,,,,,,,,,	295​	5767​
0,0,,0,0,0,0,0,	0​	0​
,	0​	0​
0,,,,,,,,,,,,,,	0​	0​
0,0,0,130,0,130,130,130,	130​	130​
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4267,1862,0,0,0,0,0,0,0,0,0,0,0,0,,,0,0,	1862​	4267​
0,0,1629,2705,3772,3167,1048,0,0,0,0,0,0,0,0,0,	1048​	3772​
0,76937,0,0,0,0,0,0,0,0,0,0,0,0,	0​	76937​
0,0,0,1047,0,1047,1047,1047,,,,	0​	1047​

 

[pawan]

@Peter_SSs The Indirect(1:xxx) creates an array of numbers from 1 to xxx in each cell. When you do this in 40K rows, you have a very heavy file which cannot be handled easily. Each calculation cycle takes 5-10 seconds to happen even on fastest of laptops. All formulae which resort to this method have to be avoided. I am also looking at some other solutions to make the file lighter.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Data	Expected Result Min (Non Zero)	Expected Result Max
2	3897,2561,0,0,0,0,0,0,,,,5767,4479,3221,0,0,0,0,0,295,295,,,,,3677,,,,,,,,,,,	295	5767
3	0,0,,0,0,0,0,0,	0	0
4	,	0	0
5	0,,,,,,,,,,,,,,	0	0
6	0,0,0,130,0,130,130,130,	0	130
7	0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4267,1862,0,0,0,0,0,0,0,0,0,0,0,0,,,0,0,	1862	4267
8	0,0,1629,2705,3772,3167,1048,0,0,0,0,0,0,0,0,0,	1048	3772
9	0,76937,0,0,0,0,0,0,0,0,0,0,0,0,	0	76937
10	0,0,0,1047,0,1047,1047,1047,,,,	0	1047
Main

Cell Formulas
Range	Formula
B2:B10	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ","</m><m>")&"</m></k>","//m[.>0]"),IFERROR(IF(MAX(f)=MIN(f),0,MIN(f)),0))
C2:C10	C2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ","</m><m>")&"</m></k>","//m[.>0]"),IFERROR(MAX(f),0))