Sorting

Gotchaman

New Member
Joined
Jul 15, 2011
Messages
25
I need to sort values (see below) in alphabetical order but first 3 characters are numeric. Is there a way to sort these in alphabetical order?

000P780
104I030
105G021
105G101
106A003
106G001
106K067
106K068
106K068
107I106
108D012
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the board...

Put a formula in an available column to extract the string after the 3rd character...
=RIGHT(A4,LEN(A4)-3)

Then sort on that column..

Like so

Excel Workbook
ABCDE
1Before SortAfter Sort
2
3Header1Header2Header1Header2
4000P780P780106A003A003
5104I030I030108D012D012
6105G021G021106G001G001
7105G101G101105G021G021
8106A003A003105G101G101
9106G001G001104I030I030
10106K067K067107I106I106
11106K068K068106K067K067
12106K068K068106K068K068
13107I106I106106K068K068
14108D012D012000P780P780
Sheet2



Hope that helps.
 
Upvote 0
<br />Sorted Results<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">106A003</td><td style=";">A003</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">108D012</td><td style=";">D012</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">106G001</td><td style=";">G001</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">105G021</td><td style=";">G021</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">105G101</td><td style=";">G101</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">104I030</td><td style=";">I030</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">107I106</td><td style=";">I106</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">106K067</td><td style=";">K067</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">106K068</td><td style=";">K068</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">106K068</td><td style=";">K068</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">000P780</td><td style=";">P780</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,4,4</font>)</td></tr></tbody></table></td></tr></table><br />

Put this formula in column B (or any empty column) and then sort on column B
 
Upvote 0

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top