Help With Sort

[Dazzawm]

I have a sheet where I want to sort either by column AK (then other criteria) or a selection of rows. I want to do it by lowest to highest but the way its working is because one number starts with 1 its putting that first like below. There will be many other permutations also.

Core Dims: L110 x D103 x W99 mm
Core Dims: L125 x D79 x W88 mm
Core Dims: L86 x D102 x W86 mm
Core Dims: L98 x D63 x W98 mm

This is how I want it

Core Dims: L86 x D102 x W86 mm
Core Dims: L98 x D63 x W98 mm
Core Dims: L110 x D103 x W99 mm
Core Dims: L125 x D79 x W88 mm

If with the above example and I select those 4 rows and I do A-Z it gives top result, if I do Z-A that's even worse.

Any ideas please?

 

[6StringJazzer]

Your problem is that in your head, these are numbers. But in Excel it is text. You either need to change the way you do data entry, then easily use AutoFilter to sort:

$scratch.xlsm
	A	B	C
1	L (mm)	D (mm)	W (mm)
2	110	103	99
3	125	79	88
4	86	102	86
5	98	63	98
sort dimensions

or add more columns to break out the numbers you want to sort, then use AutoFilter to sort

$scratch.xlsm
	A	B	C	D
1		L (mm)	D (mm)	W (mm)
2	Core Dims: L110 x D103 x W99 mm	110	103	99
3	Core Dims: L125 x D79 x W88 mm	125	79	88
4	Core Dims: L86 x D102 x W86 mm	86	102	86
5	Core Dims: L98 x D63 x W98 mm	98	63	98
sort dimensions

Cell Formulas
Range	Formula
B2:B5	B2	=VALUE(SUBSTITUTE(INDEX(TEXTSPLIT($A2," "),3),"L",""))
C2:C5	C2	=VALUE(SUBSTITUTE(INDEX(TEXTSPLIT($A2," "),5),"D",""))
D2:D5	D2	=VALUE(SUBSTITUTE(INDEX(TEXTSPLIT($A2," "),7),"W",""))

 

[fahad_ibnfurjan]

hhhhhh.xlsx
	A
1	Core Dims: L110 x D103 x W99 mm
2	Core Dims: L125 x D79 x W88 mm
3	Core Dims: L86 x D102 x W86 mm
4	Core Dims: L98 x D63 x W98 mm
5
6
7
8	Core Dims: L86 x D102 x W86 mm
9	Core Dims: L98 x D63 x W98 mm
10	Core Dims: L110 x D103 x W99 mm
11	Core Dims: L125 x D79 x W88 mm
Sort

Cell Formulas
Range	Formula
A8:A11	A8	=SORTBY($A$1:$A$4,MATCH("L"&SORT(--TRIM(TEXTBEFORE(TEXTAFTER($A$1:$A$4,": L"),"x"))),"L"&(--TRIM(TEXTBEFORE(TEXTAFTER($A$1:$A$4,": L"),"x"))),0))
Dynamic array formulas.

 

[Georgiboy]

If sorting by the first length only and your data is always formatted that way then maybe just:

=LET(r,A1:A4,SORTBY(r,--TEXTBEFORE(TEXTAFTER(r,": L",1),"x",1),1))

However, I do agree with @6StringJazzer, if your data allows then it would be good to split it out.

 

[6StringJazzer]

I have a sheet where I want to sort either by column AK (then other criteria) or a selection of rows

OK to use a formula and have the result in a different place, as above?

Or do you want the data to be sorted in place without having to make a copy of it?

If the latter then another option is VBA.

 

[fahad_ibnfurjan]

CORRECTION

SORTBY($A$1:$A$4,MATCH("L"&(--TRIM(TEXTBEFORE(TEXTAFTER($A$1:$A$4,": L"),"x"))),"L"&SORT(--TRIM(TEXTBEFORE(TEXTAFTER($A$1:$A$4,": L"),"x"))),0))

OR

=LET(S,--TRIM(TEXTBEFORE(TEXTAFTER($A$1:$A$4,": L"),"x")),x,$A$1:$A$4,SORTBY(x,S))

 

[Dazzawm]

OK to use a formula and have the result in a different place, as above?

Or do you want the data to be sorted in place without having to make a copy of it?

If the latter then another option is VBA.

Sorry for the late reply. Yes I think VBA is the answer as I don't really want helper columns or copies etc...

 

[Georgiboy]

You could try the below, it currently sorts from AK2 to the last used row in AK:

Sub Sorter()
    Dim rng As Range, var As Variant, rAddr As String
    
    Set rng = Range("AK2", Cells(Rows.Count, "AK").End(xlUp)) ' change range to suit
    rAddr = rng.Address(, , , 1)
    var = Evaluate("SORTBY(" & rAddr & ",--TEXTBEFORE(TEXTAFTER(" & rAddr & ","": L"",1),""x"",1),1)")
    rng = var
End Sub

 

[Dazzawm]

You could try the below, it currently sorts from AK2 to the last used row in AK:

Sub Sorter()
    Dim rng As Range, var As Variant, rAddr As String
   
    Set rng = Range("AK2", Cells(Rows.Count, "AK").End(xlUp)) ' change range to suit
    rAddr = rng.Address(, , , 1)
    var = Evaluate("SORTBY(" & rAddr & ",--TEXTBEFORE(TEXTAFTER(" & rAddr & ","": L"",1),""x"",1),1)")
    rng = var
End Sub

Thanks, what if I want to add other criteria. i.e sort by AK then AE then C etc...?

 

[Georgiboy]

That would depend on what was in AE & C and whether you wanted to search by part of that string or the whole cell value.

Might be best to show us more complete sample of what you want to do and the size of the range to be sorted.