# Sorting Alphanumeric data

#### DoggettsBG

##### New Member
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>

### 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
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 />

#### DoggettsBG

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

#### VBA Geek

##### MrExcel MVP
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

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

##### New Member
Formula for C2 says its missing a ( somewhere

#### DoggettsBG

##### New Member
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

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

You're welcome and thank you for the feedback.

Markmzz

Replies
3
Views
385
Replies
1
Views
50
Replies
2
Views
995
Replies
4
Views
655
Replies
0
Views
73

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