How to sort

jjblackisback

New Member
Joined
Jan 23, 2014
Messages
2
Each line below is in a cell. I would like to sort the cells by Thunnel##.
ip route 172.21.3.0 255.255.255.0 Tunnel28 220
ip route 172.21.4.0 255.255.255.0 Tunnel81 220
ip route 172.21.19.0 255.255.255.0 Tunnel47 220
ip route 172.21.20.0 255.255.252.0 Tunnel8 220
ip route 172.21.25.0 255.255.255.0 Tunnel60 220
ip route 172.21.26.0 255.255.255.0 Tunnel60 220

any ideals?

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
One way, not sure if it's the best, would be to use a helper column to extract the Tunnelxx value and then sort using that column. Another way would be to do this using a macro, which would then clear the contents of the helper column.
 
Upvote 0
The data in columns E:F have been sorted using the sort column.
Excel Workbook
ABCDEF
1DataSort ColDataSort Col
2ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220
3ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220
4ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220
5ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220
6ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220
7ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220
Sheet1
 
Upvote 0
The data in columns E:F have been sorted using the sort column.
Sheet1

*ABCDEF
1DataSort Col**DataSort Col
2ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220**ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220
3ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220**ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220
4ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220**ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220
5ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220**ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220
6ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220**ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220
7ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220**ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:304px;"><col style="width:88px;"><col style="width:36px;"><col style="width:37px;"><col style="width:304px;"><col style="width:88px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=RIGHT(A2,LEN(A2)-SEARCH("Tunnel",A2)+1)

<tbody>
</tbody>

<tbody>
</tbody>


That worked perfect, thank you very much.
 
Upvote 0
The data in columns E:F have been sorted using the sort column.
Sheet1

*ABCDEF
1DataSort Col**DataSort Col
2ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220**ip route 172.21.3.0 255.255.255.0 Tunnel28 220Tunnel28 220
3ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220**ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220
4ip route 172.21.19.0 255.255.255.0 Tunnel47 220Tunnel47 220**ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220
5ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220**ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220
6ip route 172.21.25.0 255.255.255.0 Tunnel60 220Tunnel60 220**ip route 172.21.20.0 255.255.252.0 Tunnel8 220Tunnel8 220
7ip route 172.21.26.0 255.255.255.0 Tunnel60 220Tunnel60 220**ip route 172.21.4.0 255.255.255.0 Tunnel81 220Tunnel81 220

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=RIGHT(A2,LEN(A2)-SEARCH("Tunnel",A2)+1)

<tbody>
</tbody>

<tbody>
</tbody>


That worked perfect, thank you very much.
You are welcome - thanks for the reply.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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