text to columns question

R

robfl22

Board Regular
Joined
Jan 29, 2009
Messages
87
Hi all,

Need your help. I am running a web query into yahoo sports to get updated major league baseball standings. However, every time i refresh this query, I have to spend about 20 minutes putting certain text into separate columns. The problems are with the dashes between home record, road record, and last ten(L10). Is there a way to atuomatically do this via a macro? For example, if you look at Tampa Bay Rays, their home record is 5 wins and 4 losses(5-4) I want the 5 in a separate column from the 4 without the dash and I want this to automatically happen every time I refresh the query. Thanks in advance


<TABLE class=yspcontent border=0 cellSpacing=0 cellPadding=0 width=974><TBODY><TR><TD vAlign=top width=800><TABLE border=0 cellSpacing=0 cellPadding=2 width="100%"><TBODY><TR class=yspsctbg><TD class=ysptblhdr height=18 colSpan=12>merican League</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> East</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> Tampa Bay Rays</TD><TD>14</TD><TD>5</TD><TD>.737</TD><TD>--</TD><TD>5-4</TD><TD>9-1</TD><TD>12-4</TD><TD>2-1</TD><TD>0-0</TD><TD>Won 2</TD><TD>8-2</TD></TR><TR class=ysprow2 align=right><TD align=left> New York Yankees</TD><TD>12</TD><TD>6</TD><TD>.667</TD><TD>1.5</TD><TD>5-1</TD><TD>7-5</TD><TD>4-2</TD><TD>0-0</TD><TD>8-4</TD><TD>Lost 1</TD><TD>7-3</TD></TR><TR class=ysprow1 align=right><TD align=left> Toronto Blue Jays</TD><TD>10</TD><TD>9</TD><TD>.526</TD><TD>4.0</TD><TD>4-6</TD><TD>6-3</TD><TD>4-2</TD><TD>4-3</TD><TD>2-4</TD><TD>Lost 2</TD><TD>4-6</TD></TR><TR class=ysprow2 align=right><TD align=left> Boston Red Sox</TD><TD>8</TD><TD>11</TD><TD>.421</TD><TD>6.0</TD><TD>5-8</TD><TD>3-3</TD><TD>3-7</TD><TD>3-3</TD><TD>2-1</TD><TD>Lost 1</TD><TD>4-6</TD></TR><TR class=ysprow1 align=right><TD align=left> Baltimore Orioles</TD><TD>3</TD><TD>16</TD><TD>.158</TD><TD>11.0</TD><TD>0-6</TD><TD>3-10</TD><TD>2-10</TD><TD>0-0</TD><TD>1-6</TD><TD>Won 1</TD><TD>2-8</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> Central</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> Minnesota Twins</TD><TD>13</TD><TD>6</TD><TD>.684</TD><TD>--</TD><TD>6-3</TD><TD>7-3</TD><TD>2-1</TD><TD>8-4</TD><TD>3-1</TD><TD>Lost 1</TD><TD>7-3</TD></TR><TR class=ysprow2 align=right><TD align=left> Detroit Tigers</TD><TD>10</TD><TD>9</TD><TD>.526</TD><TD>3.0</TD><TD>4-2</TD><TD>6-7</TD><TD>0-0</TD><TD>6-3</TD><TD>4-6</TD><TD>Lost 1</TD><TD>4-6</TD></TR><TR class=ysprow1 align=right><TD align=left> Cleveland Indians</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>4.5</TD><TD>4-2</TD><TD>4-8</TD><TD>0-0</TD><TD>6-6</TD><TD>2-4</TD><TD>Lost 1</TD><TD>6-4</TD></TR><TR class=ysprow2 align=right><TD align=left> Chicago White Sox</TD><TD>8</TD><TD>11</TD><TD>.421</TD><TD>5.0</TD><TD>6-6</TD><TD>2-5</TD><TD>3-4</TD><TD>2-7</TD><TD>3-0</TD><TD>Won 3</TD><TD>4-6</TD></TR><TR class=ysprow1 align=right><TD align=left> Kansas City Royals</TD><TD>7</TD><TD>11</TD><TD>.389</TD><TD>5.5</TD><TD>3-6</TD><TD>4-5</TD><TD>2-4</TD><TD>5-7</TD><TD>0-0</TD><TD>Won 1</TD><TD>4-6</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> West</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> Oakland Athletics</TD><TD>12</TD><TD>8</TD><TD>.600</TD><TD>--</TD><TD>9-5</TD><TD>3-3</TD><TD>4-3</TD><TD>2-1</TD><TD>6-4</TD><TD>Won 1</TD><TD>6-4</TD></TR><TR class=ysprow2 align=right><TD align=left> Los Angeles Angels</TD><TD>10</TD><TD>10</TD><TD>.500</TD><TD>2.0</TD><TD>6-8</TD><TD>4-2</TD><TD>6-3</TD><TD>3-5</TD><TD>1-2</TD><TD>Won 1</TD><TD>7-3</TD></TR><TR class=ysprow1 align=right><TD align=left> Seattle Mariners</TD><TD>9</TD><TD>10</TD><TD>.474</TD><TD>2.5</TD><TD>7-2</TD><TD>2-8</TD><TD>3-0</TD><TD>2-4</TD><TD>4-6</TD><TD>Lost 3</TD><TD>6-4</TD></TR><TR class=ysprow2 align=right><TD align=left> Texas Rangers</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>3.0</TD><TD>5-4</TD><TD>3-6</TD><TD>2-7</TD><TD>4-2</TD><TD>2-1</TD><TD>Won 1</TD><TD>3-7</TD></TR><TR><TD height=18 colSpan=12><SPACER type="block" height="1" width="1"></TD></TR><TR class=yspsctbg><TD class=ysptblhdr height=18 colSpan=12> National League</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> East</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> Philadelphia Phillies</TD><TD>11</TD><TD>7</TD><TD>.611</TD><TD>--</TD><TD>3-3</TD><TD>8-4</TD><TD>7-5</TD><TD>3-0</TD><TD>1-2</TD><TD>Lost 1</TD><TD>4-6</TD></TR><TR class=ysprow2 align=right><TD align=left> Florida Marlins</TD><TD>10</TD><TD>8</TD><TD>.556</TD><TD>1.0</TD><TD>4-3</TD><TD>6-5</TD><TD>4-2</TD><TD>3-4</TD><TD>3-2</TD><TD>Lost 1</TD><TD>6-4</TD></TR><TR class=ysprow1 align=right><TD align=left> Washington Nationals</TD><TD>10</TD><TD>9</TD><TD>.526</TD><TD>1.5</TD><TD>7-6</TD><TD>3-3</TD><TD>4-5</TD><TD>2-1</TD><TD>4-3</TD><TD>Won 1</TD><TD>6-4</TD></TR><TR class=ysprow2 align=right><TD align=left> New York Mets</TD><TD>9</TD><TD>9</TD><TD>.500</TD><TD>2.0</TD><TD>7-5</TD><TD>2-4</TD><TD>4-4</TD><TD>4-3</TD><TD>1-2</TD><TD>Won 3</TD><TD>7-3</TD></TR><TR class=ysprow1 align=right><TD align=left> Atlanta Braves</TD><TD>8</TD><TD>9</TD><TD>.471</TD><TD>2.5</TD><TD>5-4</TD><TD>3-5</TD><TD>1-4</TD><TD>2-1</TD><TD>5-4</TD><TD>Lost 4</TD><TD>5-5</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> Central</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> St. Louis Cardinals</TD><TD>11</TD><TD>7</TD><TD>.611</TD><TD>--</TD><TD>4-2</TD><TD>7-5</TD><TD>2-1</TD><TD>6-3</TD><TD>3-3</TD><TD>Won 1</TD><TD>5-5</TD></TR><TR class=ysprow2 align=right><TD align=left> Chicago Cubs</TD><TD>9</TD><TD>10</TD><TD>.474</TD><TD>2.5</TD><TD>3-3</TD><TD>6-7</TD><TD>2-5</TD><TD>7-5</TD><TD>0-0</TD><TD>Won 3</TD><TD>5-5</TD></TR><TR class=ysprow1 align=right><TD align=left> Milwaukee Brewers</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>3.0</TD><TD>3-6</TD><TD>5-4</TD><TD>1-2</TD><TD>5-7</TD><TD>2-1</TD><TD>Lost 3</TD><TD>5-5</TD></TR><TR class=ysprow2 align=right><TD align=left> Houston Astros</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>3.0</TD><TD>5-7</TD><TD>3-3</TD><TD>2-4</TD><TD>6-3</TD><TD>0-3</TD><TD>Won 3</TD><TD>8-2</TD></TR><TR class=ysprow1 align=right><TD align=left> Cincinnati Reds</TD><TD>8</TD><TD>11</TD><TD>.421</TD><TD>3.5</TD><TD>6-6</TD><TD>2-5</TD><TD>2-2</TD><TD>3-6</TD><TD>3-3</TD><TD>Won 1</TD><TD>3-7</TD></TR><TR class=ysprow2 align=right><TD align=left> Pittsburgh Pirates</TD><TD>7</TD><TD>11</TD><TD>.389</TD><TD>4.0</TD><TD>5-4</TD><TD>2-7</TD><TD>0-0</TD><TD>3-6</TD><TD>4-5</TD><TD>Lost 6</TD><TD>3-7</TD></TR><TR class=ysptblthbody1 align=right><TD class=yspdetailttl height=18 width="22%" align=left> West</TD><TD class=yspdetailttl width="5%">W</TD><TD class=yspdetailttl width="5%">L</TD><TD class=yspdetailttl width="7%">Pct</TD><TD class=yspdetailttl width="7%">GB</TD><TD class=yspdetailttl width="7%">Home</TD><TD class=yspdetailttl width="7%">Road</TD><TD class=yspdetailttl width="8%">East</TD><TD class=yspdetailttl width="8%">Cent</TD><TD class=yspdetailttl width="8%">West</TD><TD class=yspdetailttl width="9%">Streak</TD><TD class=yspdetailttl width="7%">L10</TD></TR><TR class=ysprow1 align=right><TD align=left> San Diego Padres</TD><TD>11</TD><TD>7</TD><TD>.611</TD><TD>--</TD><TD>7-2</TD><TD>4-5</TD><TD>1-2</TD><TD>2-1</TD><TD>8-4</TD><TD>Lost 1</TD><TD>8-2</TD></TR><TR class=ysprow2 align=right><TD align=left> San Francisco Giants</TD><TD>10</TD><TD>8</TD><TD>.556</TD><TD>1.0</TD><TD>6-3</TD><TD>4-5</TD><TD>2-1</TD><TD>7-2</TD><TD>1-5</TD><TD>Lost 1</TD><TD>4-6</TD></TR><TR class=ysprow1 align=right><TD align=left> Colorado Rockies</TD><TD>9</TD><TD>9</TD><TD>.500</TD><TD>2.0</TD><TD>5-3</TD><TD>4-6</TD><TD>6-6</TD><TD>1-2</TD><TD>2-1</TD><TD>Won 1</TD><TD>4-6</TD></TR><TR class=ysprow2 align=right><TD align=left> Arizona Diamondbacks</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>3.0</TD><TD>7-5</TD><TD>1-5</TD><TD>2-1</TD><TD>3-3</TD><TD>3-6</TD><TD>Won 1</TD><TD>3-7</TD></TR><TR class=ysprow1 align=right><TD align=left> Los Angeles Dodgers</TD><TD>8</TD><TD>10</TD><TD>.444</TD><TD>3.0</TD><TD>4-2</TD><TD>4-8</TD><TD>2-4</TD><TD>2-4</TD><TD>4-2</TD><TD>Lost 1</TD><TD>5-5</TD></TR><TR><TD height=7 colSpan=12><SPACER type="block" height="1" width="1"></TD></TR><TR><TR><TD class=yspscores height=7 colSpan=12>x-Clinched Playoff Spot; y-Division Champ Last updated Sunday, Apr 25, 2010 7:30 pm EDT
</TD></TR></TBODY></TABLE></TD><TD width=14> </TD></TR></TBODY></TABLE>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
This souds like the kind of thing that can be recorded into a macro. Turn on the recorder and let it record YOU fixing the data one time to look the way you want. Then post that code here and we'll help you streamline it and turn it into an automatic macro that runs each time the qeb query refreshes.
 
Upvote 0
JB, I think i did this right. Thanks for your help

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/25/2010 by Bob
'
'
ActiveWindow.SmallScroll Down:=15
Range("G4:L41").Select
Range("L41").Activate
Selection.Cut Destination:=Range("J4:O41")
Range("F4:F41").Select
Selection.TextToColumns Destination:=Range("F4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("J4:J41").Select
Selection.Cut Destination:=Range("H4:H41")
Range("H4:H41").Select
Selection.TextToColumns Destination:=Range("H4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("K4:K41").Select
Selection.Cut Destination:=Range("J4:J41")
Range("L4:O41").Select
Range("O41").Activate
Selection.Cut Destination:=Range("N4:Q41")
Range("J4:J41").Select
Selection.TextToColumns Destination:=Range("J4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("N4:N41").Select
Selection.Cut Destination:=Range("L4:L41")
Range("L4:L41").Select
Selection.TextToColumns Destination:=Range("L4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("O4:O41").Select
Selection.Cut Destination:=Range("N4:N41")
Range("N4:N41").Select
Selection.TextToColumns Destination:=Range("N4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=1
Range("P4:S41").Select
Selection.Cut Destination:=Range("R4:U41")
Range("R4:R41").Select
Range("R41").Activate
Selection.Cut Destination:=Range("P4:P41")
Range("P4:P41").Select
Selection.TextToColumns Destination:=Range("P4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("S4:S41").Select
Selection.Cut Destination:=Range("R4:R41")
Range("R4:R41").Select
ActiveWindow.SmallScroll Down:=-12
Selection.TextToColumns Destination:=Range("R4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=20
Range("W1").Select
With Selection.QueryTable
.Connection = _
"URL;http://sports.yahoo.com/mlb/standings?type=regular&year=season_2010"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("AC4:AH41").Select
Selection.Cut Destination:=Range("AG4:AL41")
Range("AB4:AB41").Select
Range("AB41").Activate
Selection.TextToColumns Destination:=Range("AB4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("AG4:AG41").Select
Range("AG41").Activate
Selection.Cut Destination:=Range("AD4:AD41")
Range("AD4:AD41").Select
Selection.TextToColumns Destination:=Range("AD4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("AH4:AH41").Select
ActiveWindow.SmallScroll ToRight:=2
Range("AH4:AL41").Select
Range("AL41").Activate
Selection.Cut Destination:=Range("AF4:AJ41")
Range("AF4:AJ41").Select
ActiveWindow.SmallScroll Down:=21
Range("AK28").Select
ActiveWindow.SmallScroll Down:=-36
Range("AM1").Select
With Selection.QueryTable
.Connection = "URL;http://www.cbssports.com/mlb/standings/expanded"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "23,25,""button-bar"",27,28,29,30,31,32,33,34,35"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll ToRight:=13
ActiveWindow.SmallScroll Down:=9
Range("AS12:BA59").Select
Selection.Cut Destination:=Range("BB12:BJ59")
Range("AR12:AR59").Select
Range("AR59").Activate
Selection.TextToColumns Destination:=Range("AR12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BB12:BB59").Select
Range("BB59").Activate
Selection.Cut Destination:=Range("AT12:AT59")
Range("AT12:AT59").Select
Selection.TextToColumns Destination:=Range("AT12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BC12:BC59").Select
Selection.Cut Destination:=Range("AV12:AV59")
Range("AV12:AV59").Select
Selection.TextToColumns Destination:=Range("AV12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BD12:BD59").Select
Selection.Cut Destination:=Range("AX12:AX59")
Range("AX12:AX59").Select
Selection.TextToColumns Destination:=Range("AX12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BE12:BE59").Select
Selection.Cut Destination:=Range("AZ12:AZ59")
Range("AZ12:AZ59").Select
Selection.TextToColumns Destination:=Range("AZ12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BF12:BF59").Select
ActiveWindow.SmallScroll Down:=-12
Selection.Cut Destination:=Range("BB12:BB59")
Range("BB12:BB59").Select
Selection.TextToColumns Destination:=Range("BB12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BG12:BG59").Select
Selection.Cut Destination:=Range("BD12:BD59")
Range("BD12:BD59").Select
Selection.TextToColumns Destination:=Range("BD12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("BH12:BH59").Select
Selection.Cut Destination:=Range("BF12:BF59")
Range("BF12:BF59").Select
Selection.TextToColumns Destination:=Range("BF12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=0
Range("BI12:BJ59").Select
Selection.Cut Destination:=Range("BH12:BI59")
Range("BH12:BI59").Select
End Sub
 
Upvote 0
1) Right-click on the sheet tab name and select VIEW CODE, this will open the sheet module in the VBEditor

2) Paste in this sheet-event macro into the module that appears:
Code: 
Option Explicit

Private Sub Worksheet_Change()
Application.EnableEvents = False

Range("G4:L41").Cut Destination:=Range("J4:O41")
Range("F4:F41").TextToColumns Destination:=Range("F4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("J4:J41").Cut Destination:=Range("H4:H41")
Range("H4:H41").TextToColumns Destination:=Range("H4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("K4:K41").Cut Destination:=Range("J4:J41")
Range("L4:O41").Cut Destination:=Range("N4:Q41")

Range("J4:J41").TextToColumns Destination:=Range("J4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("N4:N41").Cut Destination:=Range("L4:L41")
Range("L4:L41").TextToColumns Destination:=Range("L4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("O4:O41").Cut Destination:=Range("N4:N41")
Range("N4:N41").TextToColumns Destination:=Range("N4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("P4:S41").Cut Destination:=Range("R4:U41")
Range("R4:R41").Cut Destination:=Range("P4:P41")
Range("P4:P41").TextToColumns Destination:=Range("P4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("S4:S41").Cut Destination:=Range("R4:R41")
Range("R4:R41").TextToColumns Destination:=Range("R4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("W1").Select
With Selection.QueryTable
    .Connection = _
    "URL;http://sports.yahoo.com/mlb/standings?type=regular&year=season_2010"
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "4"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = True
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Range("AC4:AH41").Cut Destination:=Range("AG4:AL41")
Range("AB4:AB41").TextToColumns Destination:=Range("AB4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("AG4:AG41").Cut Destination:=Range("AD4:AD41")
Range("AD4:AD41").TextToColumns Destination:=Range("AD4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("AH4:AL41").Cut Destination:=Range("AF4:AJ41")

With Selection.QueryTable
    .Connection = "URL;http://www.cbssports.com/mlb/standings/expanded"
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "23,25,""button-bar"",27,28,29,30,31,32,33,34,35"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = True
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Range("AS12:BA59").Cut Destination:=Range("BB12:BJ59")
Range("AR12:AR59").TextToColumns Destination:=Range("AR12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BB12:BB59").Cut Destination:=Range("AT12:AT59")
Range("AT12:AT59").TextToColumns Destination:=Range("AT12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BC12:BC59").Cut Destination:=Range("AV12:AV59")
Range("AV12:AV59").TextToColumns Destination:=Range("AV12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("BD12:BD59").Cut Destination:=Range("AX12:AX59")
Range("AX12:AX59").TextToColumns Destination:=Range("AX12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BE12:BE59").Cut Destination:=Range("AZ12:AZ59")
Range("AZ12:AZ59").TextToColumns Destination:=Range("AZ12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BF12:BF59").Cut Destination:=Range("BB12:BB59")
Range("BB12:BB59").TextToColumns Destination:=Range("BB12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BG12:BG59").Cut Destination:=Range("BD12:BD59")
Range("BD12:BD59").TextToColumns Destination:=Range("BD12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BH12:BH59").Cut Destination:=Range("BF12:BF59")
Range("BF12:BF59").TextToColumns Destination:=Range("BF12"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="-", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
Range("BI12:BJ59").Cut Destination:=Range("BH12:BI59")
Application.EnableEvents = True
End Sub

3) Close the editor and save as a macro-enabled workbook

Next time the data updates, this macro will trigger itself. Be careful, it will trigger any time ANY cell changes on this particular sheet, so if you manually edit something, it will go all over again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
E
  • Question Question
If statement
Replies
4
Views
107
xlhelp15
xlhelp15
Scott Huish
  • Solved
Creating an array based on number of elements -- MAKEARRAY?
Replies
4
Views
184
Scott Huish
Scott Huish
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
142
hagia_sofia
hagia_sofia
E
  • Solved
If formula
Replies
3
Views
55
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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