in microsoft excel: alpha by name, keeping #’s

October 3, 2011

I want to alphabetize a list by channel names, keeping the channel #’s, please advise, such as:
2 WCBS New York (CBS)

3 WPXN New York (ION)

4 WNBC New York (NBC)

5 WNYW New York (FOX)

6 Univision

7 WABC New York (ABC)

8 New York 1 News
9 My9 New York (MNT-WWOR)

10 WLNY Riverhead (IND)
11 WPIX (11) New York

12 News 12 Brooklyn
13 WNET New York (PBS)

14 MSG Varsity
15 QVC

16 WNJU Linden (Telemundo)

17 TeleFutura

18 HSN
19 WRNN Kingston (IND)
20 WMBC Newton (IND)
21 WLIW Plainview (PBS)
22 NYC Life
23 MSNBC

24 CNBC

25 CNN

26 FOX News Channel

27 Discovery Channel

28 TLC

30 The NET
31 Disney Channel

32 Cartoon Network

33 Nickelodeon

34 TV Land
35 ESPN2

36 ESPN

37 TNT

38 USA Network

39 TBS

40 FX

41 Turner Classic Movies

42 WE tv

43 AMC

44 Bravo

45 Lifetime
46 A&E

47 History

48 Syfy

49 ABC Family

50 Comedy Central

51 E!

52 VH1

53 MTV

54 BET

55 MTV2
56 Spike TV

57 Animal Planet

58 truTV

59 Religious Programming
60 SportsNet New York

61 News 12 Traffic & Weather
62 The Weather Channel

63 Galavisión
64 SoapNet
65 C-SPAN
66 C-SPAN2
67 Brooklyn Community Access TV
68 Brooklyn Community Access TV
69 Brooklyn Community Access TV

70 Brooklyn Community Access TV
71 OTB on NYC TV
72 NYC Drive
73 NYC World
74 NYC Government
75 CUNY on NYC TV
76 GSN
77 CNN Headline News
78 fuse

80 Cablevision Channel Guide
81 Oxygen
82 HBO

83 IFC

84 Showtime

85 Cinemax

86 The Movie Channel

87 MSG

88 MSG Plus

89 YES Network

90 Showtime Too

91 Flix
92 Pay Per View
93 Pay Per View
94 Playboy TV (Adult)
95 Spice Xcess (Adult)
96 Travel Channel

97 Food Network

98 HGTV

99 Speed Channel

100 iO Digital Channel Guide
101 BBC America
102 C-SPAN3
103 EuroNews
104 BBC World News
105 Bloomberg TV
106 FOX Business Network

108 Live Well

109 NBC NY Non-Stop
110 Universal Sports
114 Antenna TV
115 Estrella TV
116 NYS Legislative TV
118 Leased Access
120 The Hub
121 Disney XD

122 Nicktoons Network
123 Nick Jr.
124 TeenNick
125 Boomerang
131 Kids Thirteen
132 WLIW World
133 WLIW Create
134 WTBY (54)
135 EWTN
136 Daystar
137 Telecare
140 ESPN Classic
141 ESPNews

142 FOX Soccer Channel
143 CBS Sports Network

144 ESPNU

145 The Golf Channel

146 VERSUS

148 NBA TV

149 MLB Network

158 National Geographic Wild

160 Bio

161 H2
Fyi: numbers & texts are in one column.

You can use the following event handler to sort as you wish. This assumes the data is in column A and column B is available for temp use.

Copy the following event handler to the clipboard:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i, LastRow
LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = 1 To LastRow
For j = 1 To Len(Cells(i, “A”))
If Not IsNumeric(Mid(Cells(i, “A”), j, 1)) Then
Cells(i, “B”).Value = Right(Cells(i, “A”), Len(Cells(i, “A”)) - j)
GoTo here
End If
Next
here:
Next
Columns(”A:B”).Select
Selection.Sort Key1:=Range(”B1″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(”B:B”).ClearContents
Range(”A1″).Select
End Sub

Select the worksheet containing the data and right click the sheet tab.

Select ‘View Code’.

Paste the event handler into the editing area to the right.

Close the VBE and return to the worksheet.

Double click any cell to sort the data. If additional data is added, just double click any cell again.

Will this do:

46A&E
49ABC Family
43AMC
57Animal Planet
114Antenna TV
101BBC America
104BBC World News
54BET
160Bio
105Bloomberg TV
125Boomerang
44Bravo
67Brooklyn Community Access TV
68Brooklyn Community Access TV
69Brooklyn Community Access TV
70Brooklyn Community Access TV
80Cablevision Channel Guide
32Cartoon Network
143CBS Sports Network
85Cinemax
24CNBC
25CNN
77CNN Headline News
50Comedy Central
65C-SPAN
66C-SPAN2
102C-SPAN3
75CUNY on NYC TV
136Daystar
27Discovery Channel
31Disney Channel
121Disney XD
51E!
36ESPN
140ESPN Classic
35ESPN2
141ESPNews
144ESPNU
115Estrella TV
103EuroNews
135EWTN
91Flix
97Food Network
106FOX Business Network
26FOX News Channel
142FOX Soccer Channel
78fuse
40FX
63Galavisión
76GSN
161H2
82HBO
98HGTV
47History
18HSN
83IFC
100iO Digital Channel Guide
131Kids Thirteen
118Leased Access
45Lifetime
108Live Well
149MLB Network
87MSG
88MSG Plus
14MSG Varsity
23MSNBC
53MTV
55MTV2
9My9 New York (MNT-WWOR)
158National Geographic Wild
148NBA TV
109NBC NY Non-Stop
8New York 1 News
12News 12 Brooklyn
61News 12 Traffic & Weather
123Nick Jr.
33Nickelodeon
122Nicktoons Network
72NYC Drive
74NYC Government
22NYC Life
73NYC World
116NYS Legislative TV
71OTB on NYC TV
81Oxygen
92Pay Per View
93Pay Per View
94Playboy TV (Adult)
15QVC
59Religious Programming
84Showtime
90Showtime Too
64SoapNet
99Speed Channel
95Spice Xcess (Adult)
56Spike TV
60SportsNet New York
48Syfy
39TBS
124TeenNick
137Telecare
17TeleFutura
145The Golf Channel
120The Hub
86The Movie Channel
30The NET
62The Weather Channel
28TLC
37TNT
96Travel Channel
58truTV
41Turner Classic Movies
34TV Land
110Universal Sports
6Univision
38USA Network
146VERSUS
52VH1
7WABC New York (ABC)
2WCBS New York (CBS)
42WE tv
133WLIW Create
21WLIW Plainview (PBS)
132WLIW World
10WLNY Riverhead (IND)
20WMBC Newton (IND)
4WNBC New York (NBC)
13WNET New York (PBS)
16WNJU Linden (Telemundo)
5WNYW New York (FOX)
11WPIX (11) New York
3WPXN New York (ION)
19WRNN Kingston (IND)
134WTBY (54)
89YES Network