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>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Son

Active Member
Joined
Mar 19, 2010
Messages
281
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

jjblackisback

New Member
Joined
Jan 23, 2014
Messages
2
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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
Top