Extract unique numeric values from a string and range? with Sorting if possible?

[MichaelRSnow]

Hi, not seen this combination before but anyone know how you can use a formula to extract numeric values from a string and range and sort it smallest to largest in a string within a another cell i.e.

Column A
31
57,77
77
81
81,95,96
77,79,81
38
71,81
97
Result would be = 32, 38, 57, 71, 77, 79, 95, 96, 97

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Result would be = 32, 38, 57, 71, 77, 79, 95, 96, 97

Can you explain the logic of that result. First question would be: Where does the 32 come from?

 

[MichaelRSnow]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry using MicroSoft Excel 365

 

[MichaelRSnow]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Can you explain the logic of that result. First question would be: Where does the 32 come from?

Sorry typo, should be 31 not 32 at the start

 

[Peter_SSs]

Try

22 03 10.xlsm
	A	B	C
1	31		31, 38, 57, 71, 77, 79, 81, 95, 96, 97
2	57,77
3	77
4	81
5	81,95,96
6	77,79,81
7	38
8	71,81
9	97
Sort Nums

Cell Formulas
Range	Formula
C1	C1	=TEXTJOIN(", ",1,SORT(UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(",",1,A1:A9),",","</c><c>")&"</c></p>","//c"))))

 

[Fluff]

Sorry using MicroSoft Excel 365

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C
1
2	31		31,38,57,71,77,79,81,95,96,97
3	57,77
4	77
5	81
6	81,95,96
7	77,79,81
8	38
9	71,81
10	97
Main

Cell Formulas
Range	Formula
C2	C2	=TEXTJOIN(",",,UNIQUE(SORT(FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(",",,A2:A10),",","</m><m>")&"</m></k>","//m"))))

 

[MichaelRSnow]

Try

22 03 10.xlsm
	A	B	C
1	31		31, 38, 57, 71, 77, 79, 81, 95, 96, 97
2	57,77
3	77
4	81
5	81,95,96
6	77,79,81
7	38
8	71,81
9	97
Sort Nums

Cell Formulas
Range	Formula
C1	C1	=TEXTJOIN(", ",1,SORT(UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(",",1,A1:A9),",","</c><c>")&"</c></p>","//c"))))

Yeah that is perfect, thanks Peter, works a treat.

 

[MichaelRSnow]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C
1
2	31		31,38,57,71,77,79,81,95,96,97
3	57,77
4	77
5	81
6	81,95,96
7	77,79,81
8	38
9	71,81
10	97
Main

Cell Formulas
Range	Formula
C2	C2	=TEXTJOIN(",",,UNIQUE(SORT(FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(",",,A2:A10),",","</m><m>")&"</m></k>","//m"))))

Also works, thanks Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.