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.

 

[Fluff]

Could you have value like 540 rather than 054?

 

[pawan]

Could you have value like 540 rather than 054?

It has to be interpreted by following the rule that three characters equals one data point. That being the overarching constraint, each data point could be upto 999 being of three digits. So the answer is yes it can be 540 but if three characters make one datapoint, the previous 51 characters are all zeros and have to be ignored making a combination of "540" non feasible. So only feasible possibility is "054" followed by "023". "540" followed by "230" is not feasible. I hope I am able to clarify.

 

[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:C5	B2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),{"0","0"}),,-1,1))
Dynamic array formulas.

 

[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:C5	B2	=LET(m,MID(A2,SEQUENCE(,LEN(A2)/3,,3),3),SORT(FILTER(m,(ISNUMBER(m+1))*(m<>"000"),{"0","0"}),,-1,1))
Dynamic array formulas.

Works for maximum. What about minimum?

 

[Fluff]

What about minimum?

In what way? as you can see in post#4 it returns both the max & min?

 

[pawan]

In what way? as you can see in post#4 it returns both the max & min?

Sorry. Please elaborate. It returned only the maximum value. What change do I need to make to return the minimum value?

 

[Fluff]

Please elaborate.

Look at post#4 & you can see that both values are present.
Did you use Ctrl Shift Enter to confirm the formula or just Enter?

 

[pawan]

I did Ctrl+Shift+Enter and I got only the maximum value. I seem to be missing something.

 

[Fluff]

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