Sorting Alphanumeric data

[DoggettsBG]

I have a list of unit numbers for vehicles. Some are simple 1,2,3. Some are 16A, B-1, ME-1, FOB-1, etc. How can I format it to be in order. When I do the sort function it looks like this:

1

2

10

16

20

100

16a

B-1

B-10

FB-1

FB-10

FB-12

FB-4

FOB-1

FOB-2

IN-1

IN-10

IN-12

IN-13

IN-20

ME-2

OR-6

PU-1

PU-OW2

SD-2

SF-1

V-1

<colgroup><col></colgroup><tbody>
</tbody>

I need it to look like this:

1

2

10

16

16a

20

100

B-1

B-10

FB-1

FB-4

FB-10

FB-12

FOB-1

FOB-2

IN-1

IN-10

IN-12

IN-13

IN-20

ME-2

OR-6

PU-1

PU-OW2

SD-2

SF-1

V-1

<colgroup><col></colgroup><tbody>
</tbody>

Ive tried several formulas and either it sorts correct with the ones ending in "A" but makes it only go in order of the first number in the cell so 2 now comes after 19 etc. Or the other formula I used puts them in order with the 2 being after 3 but now all units with an "A" after are on the bottom

<colgroup><col></colgroup><tbody>
</tbody>

 

[VBA Geek]

Given your data, if you do something like the below

where column B2 has the formula: =IF(ISNUMBER(SEARCH("-",A2)),LEFT(A2,SEARCH("-",A2)-1),IF(NOT(ISNUMBER(RIGHT(A2,1)+0)),LEFT(A2,LEN(A2)-1),A2))
and C2 has the formula =IF(ISNUMBER(SEARCH("-",A2)),MID(A2,LEN(B2)+2,255),IF(NOT(ISNUMBER(RIGHT(A2,1)+0)),RIGHT(A2,1),""))

then your can sort all 3 column by B and C choosing Sort anything that looks like a number as a number

Excel 2013/2016
	A	B	C
1	RAW	F1	F2
2	1	1
3	2	2
4	10	10
5	16	16
6	20	20
7	100	100
8	16a	16	a
9	B-1	B	1
10	B-10	B	10
11	FB-1	FB	1
12	FB-10	FB	10
13	FB-12	FB	12
14	FB-4	FB	4
15	FOB-1	FOB	1
16	FOB-2	FOB	2
17	IN-1	IN	1
18	IN-10	IN	10
19	IN-12	IN	12
20	IN-13	IN	13
21	IN-20	IN	20
22	ME-2	ME	2
23	OR-6	OR	6
24	PU-1	PU	1
25	PU-OW2	PU	OW2
26	SD-2	SD	2
27	SF-1	SF	1
28	V-1	V	1
Sheet2

 

[DoggettsBG]

Do you see how in your example FB-4 is after FB-12? I need it to be before it.

 

[VBA Geek]

Do you see how in your example FB-4 is after FB-12? I need it to be before it.

you just need to adjust the formulas to return a real number

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">"-",A2</font>)</font>),LEFT(<font color="Red">A2,SEARCH(<font color="Green">"-",A2</font>)-1</font>),IF(<font color="Red">NOT(<font color="Green">ISNUMBER(<font color="Purple">RIGHT(<font color="Teal">A2,1</font>)+0</font>)</font>),--LEFT(<font color="Green">A2,LEN(<font color="Purple">A2</font>)-1</font>),--A2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">"-",A2</font>)</font>),IFERROR(<font color="Red">--MID(<font color="Green">A2,LEN(<font color="Purple">B2</font>)+2,255</font>),MID(<font color="Green">A2,LEN(<font color="Purple">B2</font>)+2,255</font>)</font>),IF(<font color="Red">NOT(<font color="Green">ISNUMBER(<font color="Purple">RIGHT(<font color="Teal">A2,1</font>)+0</font>)</font>),RIGHT(<font color="Green">A2,1</font>),""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	A	B	C
1	RAW	F1	F2
2	1	1
3	2	2
4	10	10
5	16	16
6	16a	16	a
7	20	20
8	100	100
9	B-1	B	1
10	B-10	B	10
11	FB-1	FB	1
12	FB-4	FB	4
13	FB-10	FB	10
14	FB-12	FB	12
15	FOB-1	FOB	1
16	FOB-2	FOB	2
17	IN-1	IN	1
18	IN-10	IN	10
19	IN-12	IN	12
20	IN-13	IN	13
21	IN-20	IN	20
22	ME-2	ME	2
23	OR-6	OR	6
24	PU-1	PU	1
25	PU-OW2	PU	OW2
26	SD-2	SD	2
27	SF-1	SF	1
28	V-1	V	1
Sheet2

 

[markmzz]

Hi!

Try the formula below in B2 and copy down:

=IF(COUNT(FIND("-",A2)),
TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",20)),20))&"-"&
IFERROR(TEXT(1*RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),20),"000000"),TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),20))),
IFERROR(TEXT(1*LEFT(SUBSTITUTE(A2,"-",REPT(" ",20)),20),"000000"),
TEXT(1*MID(A2,1,SMALL(SEARCH(CHAR(ROW($65:$90)),A2),1)-1),"000000")&MID(A2,SMALL(SEARCH(CHAR(ROW($65:$90)),A2),1),20)))

	A	B	C	D	E	F	G
1	Data	Formula		Order Need		A-Z
2	1	000001		1		000001
3	2	000002		2		000002
4	10	000010		10		000010
5	16	000016		16		000016
6	20	000020		16a		000016a
7	100	000100		20		000020
8	16a	000016a		100		000100
9	B-1	B-000001		B-1		B-000001
10	B-10	B-000010		B-10		B-000010
11	FB-1	FB-000001		FB-1		FB-000001
12	FB-10	FB-000010		FB-4		FB-000004
13	FB-12	FB-000012		FB-10		FB-000010
14	FB-4	FB-000004		FB-12		FB-000012
15	FOB-1	FOB-000001		FOB-1		FOB-000001
16	FOB-2	FOB-000002		FOB-2		FOB-000002
17	IN-1	IN-000001		IN-1		IN-000001
18	IN-10	IN-000010		IN-10		IN-000010
19	IN-12	IN-000012		IN-12		IN-000012
20	IN-13	IN-000013		IN-13		IN-000013
21	IN-20	IN-000020		IN-20		IN-000020
22	ME-2	ME-000002		ME-2		ME-000002
23	OR-6	OR-000006		OR-6		OR-000006
24	PU-1	PU-000001		PU-1		PU-000001
25	PU-OW2	PU-OW2		PU-OW2		PU-OW2
26	SD-2	SD-000002		SD-2		SD-000002
27	SF-1	SF-000001		SF-1		SF-000001
28	V-1	V-000001		V-1		V-000001
29
****	*********	************	***	************	***	************	***

<tbody>
</tbody>

I hope that the suggestion above helps.

Markmzz

 

[DoggettsBG]

Formula for C2 says its missing a ( somewhere

 

[DoggettsBG]

Hi!

Try the formula below in B2 and copy down:

=IF(COUNT(FIND("-",A2)),
TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",20)),20))&"-"&
IFERROR(TEXT(1*RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),20),"000000"),TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),20))),
IFERROR(TEXT(1*LEFT(SUBSTITUTE(A2,"-",REPT(" ",20)),20),"000000"),
TEXT(1*MID(A2,1,SMALL(SEARCH(CHAR(ROW($65:$90)),A2),1)-1),"000000")&MID(A2,SMALL(SEARCH(CHAR(ROW($65:$90)),A2),1),20)))

	A	B	C	D	E	F	G
1	Data	Formula		Order Need		A-Z
2	1	000001		1		000001
3	2	000002		2		000002
4	10	000010		10		000010
5	16	000016		16		000016
6	20	000020		16a		000016a
7	100	000100		20		000020
8	16a	000016a		100		000100
9	B-1	B-000001		B-1		B-000001
10	B-10	B-000010		B-10		B-000010
11	FB-1	FB-000001		FB-1		FB-000001
12	FB-10	FB-000010		FB-4		FB-000004
13	FB-12	FB-000012		FB-10		FB-000010
14	FB-4	FB-000004		FB-12		FB-000012
15	FOB-1	FOB-000001		FOB-1		FOB-000001
16	FOB-2	FOB-000002		FOB-2		FOB-000002
17	IN-1	IN-000001		IN-1		IN-000001
18	IN-10	IN-000010		IN-10		IN-000010
19	IN-12	IN-000012		IN-12		IN-000012
20	IN-13	IN-000013		IN-13		IN-000013
21	IN-20	IN-000020		IN-20		IN-000020
22	ME-2	ME-000002		ME-2		ME-000002
23	OR-6	OR-000006		OR-6		OR-000006
24	PU-1	PU-000001		PU-1		PU-000001
25	PU-OW2	PU-OW2		PU-OW2		PU-OW2
26	SD-2	SD-000002		SD-2		SD-000002
27	SF-1	SF-000001		SF-1		SF-000001
28	V-1	V-000001		V-1		V-000001
29
****	*********	************	***	************	***	************	***

<tbody>
</tbody>

I hope that the suggestion above helps.

Markmzz

Thanks that worked! I was driving myself nuts trying to put together the formula!!

 

[markmzz]

Thanks that worked! I was driving myself nuts trying to put together the formula!!

You're welcome and thank you for the feedback.

Markmzz