Sorting Alphanumeric data

DoggettsBG

New Member
Joined
Apr 13, 2017
Messages
7
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>
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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


<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">RAW</td><td style=";">F1</td><td style=";">F2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10</td><td style="text-align: right;;">10</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">16</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">20</td><td style="text-align: right;;">20</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">100</td><td style="text-align: right;;">100</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;background-color: #FAFAFA;;">16a</td><td style="text-align: right;;">16</td><td style=";">a</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;background-color: #FAFAFA;;">B-1</td><td style=";">B</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;background-color: #FAFAFA;;">B-10</td><td style=";">B</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;background-color: #FAFAFA;;">FB-1</td><td style=";">FB</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;background-color: #FAFAFA;;">FB-10</td><td style=";">FB</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;background-color: #FAFAFA;;">FB-12</td><td style=";">FB</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="color: #333333;background-color: #FAFAFA;;">FB-4</td><td style=";">FB</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="color: #333333;background-color: #FAFAFA;;">FOB-1</td><td style=";">FOB</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="color: #333333;background-color: #FAFAFA;;">FOB-2</td><td style=";">FOB</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="color: #333333;background-color: #FAFAFA;;">IN-1</td><td style=";">IN</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="color: #333333;background-color: #FAFAFA;;">IN-10</td><td style=";">IN</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="color: #333333;background-color: #FAFAFA;;">IN-12</td><td style=";">IN</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="color: #333333;background-color: #FAFAFA;;">IN-13</td><td style=";">IN</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="color: #333333;background-color: #FAFAFA;;">IN-20</td><td style=";">IN</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="color: #333333;background-color: #FAFAFA;;">ME-2</td><td style=";">ME</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="color: #333333;background-color: #FAFAFA;;">OR-6</td><td style=";">OR</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="color: #333333;background-color: #FAFAFA;;">PU-1</td><td style=";">PU</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="color: #333333;background-color: #FAFAFA;;">PU-OW2</td><td style=";">PU</td><td style=";">OW2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="color: #333333;background-color: #FAFAFA;;">SD-2</td><td style=";">SD</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="color: #333333;background-color: #FAFAFA;;">SF-1</td><td style=";">SF</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="color: #333333;background-color: #FAFAFA;;">V-1</td><td style=";">V</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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 />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">RAW</td><td style=";">F1</td><td style=";">F2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10</td><td style="text-align: right;;">10</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">16</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;background-color: #FAFAFA;;">16a</td><td style="text-align: right;;">16</td><td style=";">a</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">20</td><td style="text-align: right;;">20</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">100</td><td style="text-align: right;;">100</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;background-color: #FAFAFA;;">B-1</td><td style=";">B</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;background-color: #FAFAFA;;">B-10</td><td style=";">B</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;background-color: #FAFAFA;;">FB-1</td><td style=";">FB</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;background-color: #FAFAFA;;">FB-4</td><td style=";">FB</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;background-color: #FAFAFA;;">FB-10</td><td style=";">FB</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="color: #333333;background-color: #FAFAFA;;">FB-12</td><td style=";">FB</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="color: #333333;background-color: #FAFAFA;;">FOB-1</td><td style=";">FOB</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="color: #333333;background-color: #FAFAFA;;">FOB-2</td><td style=";">FOB</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="color: #333333;background-color: #FAFAFA;;">IN-1</td><td style=";">IN</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="color: #333333;background-color: #FAFAFA;;">IN-10</td><td style=";">IN</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="color: #333333;background-color: #FAFAFA;;">IN-12</td><td style=";">IN</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="color: #333333;background-color: #FAFAFA;;">IN-13</td><td style=";">IN</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="color: #333333;background-color: #FAFAFA;;">IN-20</td><td style=";">IN</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="color: #333333;background-color: #FAFAFA;;">ME-2</td><td style=";">ME</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="color: #333333;background-color: #FAFAFA;;">OR-6</td><td style=";">OR</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="color: #333333;background-color: #FAFAFA;;">PU-1</td><td style=";">PU</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="color: #333333;background-color: #FAFAFA;;">PU-OW2</td><td style=";">PU</td><td style=";">OW2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="color: #333333;background-color: #FAFAFA;;">SD-2</td><td style=";">SD</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="color: #333333;background-color: #FAFAFA;;">SF-1</td><td style=";">SF</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="color: #333333;background-color: #FAFAFA;;">V-1</td><td style=";">V</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699

ADVERTISEMENT

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)))


ABCDEFG
1DataFormulaOrder NeedA-Z
210000011000001
320000022000002
41000001010000010
51600001616000016
62000002016a000016a
710000010020000020
816a000016a100000100
9B-1B-000001B-1B-000001
10B-10B-000010B-10B-000010
11FB-1FB-000001FB-1FB-000001
12FB-10FB-000010FB-4FB-000004
13FB-12FB-000012FB-10FB-000010
14FB-4FB-000004FB-12FB-000012
15FOB-1FOB-000001FOB-1FOB-000001
16FOB-2FOB-000002FOB-2FOB-000002
17IN-1IN-000001IN-1IN-000001
18IN-10IN-000010IN-10IN-000010
19IN-12IN-000012IN-12IN-000012
20IN-13IN-000013IN-13IN-000013
21IN-20IN-000020IN-20IN-000020
22ME-2ME-000002ME-2ME-000002
23OR-6OR-000006OR-6OR-000006
24PU-1PU-000001PU-1PU-000001
25PU-OW2PU-OW2PU-OW2PU-OW2
26SD-2SD-000002SD-2SD-000002
27SF-1SF-000001SF-1SF-000001
28V-1V-000001V-1V-000001
29
**********************************************************

<tbody>
</tbody>


I hope that the suggestion above helps.

Markmzz
 

DoggettsBG

New Member
Joined
Apr 13, 2017
Messages
7
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)))


ABCDEFG
1DataFormulaOrder NeedA-Z
210000011000001
320000022000002
41000001010000010
51600001616000016
62000002016a000016a
710000010020000020
816a000016a100000100
9B-1B-000001B-1B-000001
10B-10B-000010B-10B-000010
11FB-1FB-000001FB-1FB-000001
12FB-10FB-000010FB-4FB-000004
13FB-12FB-000012FB-10FB-000010
14FB-4FB-000004FB-12FB-000012
15FOB-1FOB-000001FOB-1FOB-000001
16FOB-2FOB-000002FOB-2FOB-000002
17IN-1IN-000001IN-1IN-000001
18IN-10IN-000010IN-10IN-000010
19IN-12IN-000012IN-12IN-000012
20IN-13IN-000013IN-13IN-000013
21IN-20IN-000020IN-20IN-000020
22ME-2ME-000002ME-2ME-000002
23OR-6OR-000006OR-6OR-000006
24PU-1PU-000001PU-1PU-000001
25PU-OW2PU-OW2PU-OW2PU-OW2
26SD-2SD-000002SD-2SD-000002
27SF-1SF-000001SF-1SF-000001
28V-1V-000001V-1V-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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,075
Messages
5,526,667
Members
409,714
Latest member
diamondjoechubbs

This Week's Hot Topics

Top