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.

 

[pawan]

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))

Super Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[pawan]

I have another related problem. Not sure if I should start a new thread on this. Please advice.

Here, there is no comma separation but problem is same i.e. of extracting max and min values out of a concatenated data string. The data is below.

Data	Expected Minimum	Expected Maximum
061031000000000000000000DDDDDDDDDXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD	031	061
XXXXXXDDDXXXXXX000000XXX	0​	0​
XXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDXXXDDDXXXDDDXXXXXXDDDXXXXXXXXXXXX	0​	0​
000000000000000000000000000000000000000000000000000054023000000000000000000000000000000000000DDDDDD000000	023	054

Each data point in the column "Data" is itself a concatenated string of several data points, each having a character length of 3 characters. These could be a 3 digit number or there could be "000" data point which means "no data". Similarly "DDD" or "XXX" also refer to no data being available. What is needed is a max and min of data points available.

So in the first row, 061 is the first data point, 031 is the second data point and so on. In the first row only two data points are available and 061 is maximum and 031 is minimum.

In the second and third row, there are no data points

In the last row 054 and 023 are the two data points and the max and min are obviously 054 and 023 respectively

 

[Fluff]

This will need a new thread. Thanks

 

[Peter_SSs]

In relation to the MIN/MAX question, you may not have it yet but there is a new TEXTSPLIT function being rolled out to 365 subscribers. With it, you would also have options like this.

22 04 21.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
Min Max

Cell Formulas
Range	Formula
B2:B10	B2	=LET(ts,(0&TEXTSPLIT(A2,","))+0,IFNA(MIN(FILTER(ts,(ts<>0)*(ts<>C2),NA())),0))
C2:C10	C2	=LET(ts,(0&TEXTSPLIT(A2,","))+0,IFNA(MAX(FILTER(ts,ts<>0,NA())),0))