Sorting similar data in cells

Question

I’m, trying to sort this using the custom label. I want to start LL3,LL2,LL3,L1,L2, ECT…

But every time I try to sort L1 out I get L1,L10,111,12 how do I tell excel to only sort L1 and so on?

Thanks in advance

Pt	Type	Point Name	Device Type	Point Type	Custom Label
521	EXT	13:M1-30	PHOTO	VSMOKE	L10 CHAMBER A BY JURY SUITE DR 13:M1-30
524	EXT	1:SIG15	SIGB	RVISUAL	LL3 VISUAL CIRCUIT L1  1:SIG15
558	EXT	13:M1-41	RZAM	RELAY	L11 SMOKE EVAC START  13:M1-41
576	EXT	1:M1-28	PHOTO	SDUCT	LL2 AHU-L1.1  1:M1-28
577	EXT	1:M1-29	RZAM	RELAY	LL2 AHU-L1.1  1:M1-29
624	EXT	13:M1-31	PHOTO	VSMOKE	L10 CHAMBER A BY RECEIPTION DR 13:M1-31
637	EXT	2:SIG15	SIGB	RVISUAL	LL2 STROBES L1  2:SIG15
669	EXT	2:M1-3	PHOTO	SDUCT	LL2 AHU-L1.2  2:M1-3
670	EXT	2:M1-4	RZAM	RELAY	LL2 AHU-L1.2  2:M1-4
693	EXT	2:M1-30	PHOTO	SDUCT	LL2 AHU-L1-2  2:M1-30
733	EXT	3:SIG3	SIGB	PHONE	LL1 FLOOR FIRE PHONES  3:SIG3
734	EXT	3:SIG4	SIGB	PHONE	LL1 ELEVATOR 14  3:SIG4
735	EXT	3:SIG5	SIGB	RVISUAL	LL1 SPARE VISUAL CIRCUIT  3:SIG5
736	EXT	3:SIG6	SIGB	RVISUAL	LL1 VISUAL SYNC TRIGGER  3:SIG6
737	EXT	3:SIG7	SIGB	RVISUAL	LL1 SPARE VISUAL CIRCUIT  3:SIG7
738	EXT	3:SIG8	SIGB	RVISUAL	LL1 SPARE VISUAL CIRCUIT  3:SIG8
739	EXT	3:SIG9	CHAN4B	SPEAKER	LL1 FLOOR MESSAGE SELECT  3:SIG9
740	EXT	3:SIG10	CHAN4B	SPEAKER	LL1 STAIRWELL MESSAGE SELECT  3:SIG10
741	EXT	3:SIG15	SIGB	RVISUAL	LL1 STROBES CIRCUIT L1  3:SIG15
742	EXT	3:SIG16	SIGB	RVISUAL	LL1 STROBES CIRCUIT L2  3:SIG16
743	EXT	3:SIG17	SIGB	RVISUAL	LL1 STROBES CIRCUIT L3  3:SIG17
744	EXT	3:SIG18	SIGB	RVISUAL	LL1 STROBES CIRCUIT L4  3:SIG18
745	EXT	3:SIG19	SIGB	RVISUAL	LL1 STROBES CIRCUIT L5  3:SIG19
746	EXT	3:SIG20	SIGB	RVISUAL	LL1 STROBES CIRCUIT L6  3:SIG20

Answer 1

Re:  sorting using a custom sort list

This seems to work...

1.

Add an adjacent "helper" column to your data containing a formula similar to...

=TRIM(LEFT(G3,3))

Copy and paste values. <<<<<<

2.

Add a custom sort list in the order desired.

(File | Options | Advanced -"Edit Custom List")

Mine looks like this...

'---

LL3

LL2

LL1

L1

L2

L3

L4

L5

L6

L7

L8

L9

L10

L11

L12

L13

L14

L15

L16

L17

L18

L19

L20

L21

L22

'---

3.

Sort the data using the new column and the new custom list.

4.

Before sort...

5.

After sort...

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

OssieMac

This is a high-rise building fire system point list. I'm trying to get it so when I'm testing it goes LL3,LL2,LL3,L1,L2,L3,L4...L22 on my test paper so I can check off easy..

Is there a way to filter only "L1" or "LL1" ?

Because if i filter l1 them all ll1 and l10, l11, l12, .. ect come up also

Thanks****

Answer 3

I want to sort all "LL3" , then all "LL2" then all LL1 then all L1 then all L2

the problem I'm having is there levels go up to L22 so when I sort in ascending order L1,L10,L11 sort in that order. I would like LL1, LL2, LL3, L1, L2, L3.. If i can't sort the LL1 seperate from the L1 i can delete the LL1 then add back later.

Is there a way to filter only "L1" or "LL1" ?

Because if i filter l1 them all ll1 and l10, l11, l12, .. ect come up also

thanks

Answer 4

Re:  sorting by the numbers

Excel sorts numbers and text separately; numbers come before any text

Excel sorts text and alpha/numeric entries in a  character by character sequence - so all 1st characters are sorted then the 2nd and so on.  That is why you can't get the sequence you need.

I don't understand your "requested" sort sequence, but assuming you want the data in numeric sequence, here is the result using my commercial "Special Sort" add-in (sorting by the first group of numbers inside the text)...

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 5

Not sure it can be done but I am hoping it might be possible by using helper columns.

First need some more information. You have 3 cells that only contain one "L" an the remainder have two "L's".

The ones with 1 "L" are:

L10 CHAMBER A BY JURY SUITE DR 13:M1-30
L10 CHAMBER A BY RECEIPTION DR 13:M1-31
L11 SMOKE EVAC START  13:M1-41

Where are these supposed to fit in the sort? Because they only have the one "L" they will always come before two "L's".