TEXTSPLIT and SORT

[nburaq]

Hi Gents,
I have combined intervals with "/" and I would like to split them in an ascending order is it possible to do that?
A2 B2 C2 D2 E2 F2 G2 H2 I2

4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years

4 Weeks

6 Weeks

12 Weeks

6 Months

1 Year

2 Years

3 Months

3 Years

I can split the text with Textsplit function but when I use the Sort Function it doesnt sort like 4 Weeks 6 Weeks 12 Weeks 3 Months 6 Months 1 Year 2 Years 3 Years. Maybe I can convert to splitted text to days for each column and then sort again?

Already Thanks for all comments and help

 

[JvdV]

Book1
	A	B	C	D	E	F	G	H	I
1	4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years	4 Weeks	6 Weeks	12 Weeks	3 Months	6 Months	1 Year	2 Years	3 Years
Sheet3

Cell Formulas
Range	Formula
B1:I1	B1	=LET(x,TEXTSPLIT(A1," / "),SORTBY(x,FIND(LEFT(TEXTAFTER(x," ")),"WMY"),,--TEXTBEFORE(x," "),))
Dynamic array formulas.

 

[nburaq]

Book1
	A	B	C	D	E	F	G	H	I
1	4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years	4 Weeks	6 Weeks	12 Weeks	3 Months	6 Months	1 Year	2 Years	3 Years
Sheet3

Cell Formulas
Range	Formula
B1:I1	B1	=LET(x,TEXTSPLIT(A1," / "),SORTBY(x,FIND(LEFT(TEXTAFTER(x," ")),"WMY"),,--TEXTBEFORE(x," "),))
Dynamic array formulas.

Hi,
Thanks for the solution it works great and what a brilliant idea!

 

[kvsrinivasamurthy]

Try A6=your data
Formula

=SORT(1*TEXTSPLIT(SUBSTITUTE(A6," Weeks",""),"/"),1,1,1)& " Weeks"

 

[nburaq]

Book1
	A	B	C	D	E	F	G	H	I
1	4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years	4 Weeks	6 Weeks	12 Weeks	3 Months	6 Months	1 Year	2 Years	3 Years
Sheet3

Cell Formulas
Range	Formula
B1:I1	B1	=LET(x,TEXTSPLIT(A1," / "),SORTBY(x,FIND(LEFT(TEXTAFTER(x," ")),"WMY"),,--TEXTBEFORE(x," "),))
Dynamic array formulas.

One thing regarding this formula if a cell does not include weeks then it sorts starting from year. how can this be fixed?

 

[nburaq]

Try A6=your data
Formula

=SORT(1*TEXTSPLIT(SUBSTITUTE(A6," Weeks",""),"/"),1,1,1)& " Weeks"

Thanks for the help but does this one only sort weeks right?

 

[JvdV]

One thing regarding this formula if a cell does not include weeks then it sorts starting from year. how can this be fixed?

I can't replicate that issue:

Book1
	A	B	C	D	E	F
1	6 Months / 1 Year / 2 Years / 3 Months / 3 Years	3 Months	6 Months	1 Year	2 Years	3 Years
Sheet3

Cell Formulas
Range	Formula
B1:F1	B1	=LET(x,TEXTSPLIT(A1," / "),SORTBY(x,FIND(LEFT(TEXTAFTER(x," ")),"WMY"),,--TEXTBEFORE(x," "),))
Dynamic array formulas.

 

[Georgiboy]

I go such a long way around sometimes, below is what i was playing about with - seems rather long winded but i wanted to sort by the {Week, Month, Year} and then by the number itself.

Book1
	A	B	C	D	E	F	G	H	I
1
2	4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years	4 Weeks	6 Weeks	12 Weeks	3 Months	6 Months	1 Year	2 Years	3 Years
3
Sheet1

Cell Formulas
Range	Formula
B2:I2	B2	=TOROW(LET(mp,WRAPROWS(TEXTSPLIT(SUBSTITUTE(A2," /","")," "),2),nm,--INDEX(mp,,1),wd,INDEX(mp,,2),words,MAP(LEFT(INDEX(mp,,2),1),LAMBDA(x,MATCH(x,{"W","M","Y"},0))),BYROW(TAKE(SORT(HSTACK(nm,wd,words),{3,1},{1,1}),,2),LAMBDA(x,TEXTJOIN(" ",,x)))))
Dynamic array formulas.

 

[nburaq]

I go such a long way around sometimes, below is what i was playing about with - seems rather long winded but i wanted to sort by the {Week, Month, Year} and then by the number itself.

Book1
	A	B	C	D	E	F	G	H	I
1
2	4 Weeks / 6 Weeks / 12 Weeks / 6 Months / 1 Year / 2 Years / 3 Months / 3 Years	4 Weeks	6 Weeks	12 Weeks	3 Months	6 Months	1 Year	2 Years	3 Years
3
Sheet1

Cell Formulas
Range	Formula
B2:I2	B2	=TOROW(LET(mp,WRAPROWS(TEXTSPLIT(SUBSTITUTE(A2," /","")," "),2),nm,--INDEX(mp,,1),wd,INDEX(mp,,2),words,MAP(LEFT(INDEX(mp,,2),1),LAMBDA(x,MATCH(x,{"W","M","Y"},0))),BYROW(TAKE(SORT(HSTACK(nm,wd,words),{3,1},{1,1}),,2),LAMBDA(x,TEXTJOIN(" ",,x)))))
Dynamic array formulas.

This one works perfect! I will not ask how because seems very complicated to me Thanks again for your help!!!

 

[kvsrinivasamurthy]

Try. This will convert into days and sort.

=SORT(7*TEXTSPLIT(SUBSTITUTE(A6," Weeks",""),"/"),1,1,1)