Finding Min and Max out of a concatenated string containing multiple values

[pawan]

I have another related problem to the Min and Max problem in a concatenated string solved in the thread below.

Min and Max on comma separated values in a cell

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

www.mrexcel.com

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; so min and max both are Zeros "0"

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

There are 40K + rows in my data table and any formula using Indirect(1:xxx) which fills numbers 1 to xxx in a cell will put "hang" my file. Hence to be avoided.

Please help. Thanks in advance.

 

[pawan]

Don't use Ctrl Shift Enter, it should just be Enter & it will spill across

Oh. OK. Now I got it. Can it be made to return only minimum or maximum at a time? Not too confident of handling spilled arrays

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	Data	Expected Minimum	Expected Maximum
2	061031000000000000000000DDDDDDDDDXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD	061	031
3	XXXXXXDDDXXXXXX000000XXX	0	0
4	XXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDXXXDDDXXXDDDXXXXXXDDDXXXXXXXXXXXX	0	0
5	000000000000000000000000000000000000000000000000000054023000000000000000000000000000000000000DDDDDD000000	054	023
Main

Cell Formulas
Range	Formula
B2:B5	B2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),INDEX(SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),"0"),,-1,1),1))
C2:C5	C2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),INDEX(SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),"0"),,1,1),1))

 

[shift-del]

As an alternative:

=IFERROR(AGGREGATE({15,14},6,1/(1/MID(A2,SEQUENZ(QUOTIENT(LEN(A2),3),,,3),3)),1),0)

 

[pawan]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	Data	Expected Minimum	Expected Maximum
2	061031000000000000000000DDDDDDDDDXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD	061	031
3	XXXXXXDDDXXXXXX000000XXX	0	0
4	XXXDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDXXXDDDXXXDDDXXXXXXDDDXXXXXXXXXXXX	0	0
5	000000000000000000000000000000000000000000000000000054023000000000000000000000000000000000000DDDDDD000000	054	023
Main

Cell Formulas
Range	Formula
B2:B5	B2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),INDEX(SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),"0"),,-1,1),1))
C2:C5	C2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),INDEX(SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),"0"),,1,1),1))

Works like a charm. Thank you!!

 

[Fluff]

Glad to help & thanks for the feedback.