pasting data changes format

  • Thread starter Thread starter - Bobb -
  • Start date Start date
What I do with something like this is use Data>Import External Data>New Web
Query

Enter the URL in the dialog and go.

Select the range to import by clicking on the yellow "x"

Go to Options and "disable Date recognition"

Now hit Import.

The Ht is not converted to dates.

Elsewise, copy the data from the site and paste into Notepad.

Save as a *.txt file and open that in Excel which gives you the Text Import
Wizard where you set the column data format as Text.


Gord Dibben MS Excel MVP
 
Thanks Gord, but it doesn't work for me ...
http://boston.redsox.mlb.com/team/roster_40man.jsp?c_id=bos
If I try Data - External Web , it only grabs the left part of the webpage
( my "table" doesn't show a yellow arrow in it. ( java vs table ??)

when I tried the text version, I lose the alignment when imported.
here's my text file:

40-Man Roster
Pitchers B/T Ht Wt DOB
19 Josh Beckett R/R 6-5 220 05/15/80
64 Michael Bowden R/R 6-3 215 09/09/86
61 Clay Buchholz L/R 6-3 190 08/14/84
17 Manny Delcarmen R/R 6-2 205 02/16/82
73 Felix Doubront L/L 6-2 166 10/23/87
84 Miguel Gonzalez R/R 6-1 170 05/27/84
43 Devern Hansack R/R 6-2 185 02/05/78
75 Hunter Jones L/L 6-4 235 01/10/84
31 Jon Lester L/L 6-2 190 01/07/84
39 Wes Littleton R/R 6-3 200 09/02/82
48 Javier Lopez L/L 6-4 220 07/11/77
63 Justin Masterson R/R 6-6 250 03/22/85
18 Daisuke Matsuzaka R/R 6-0 185 09/13/80
37 Hideki Okajima L/L 6-1 195 12/25/75
58 Jonathan Papelbon R/R 6-4 225 11/23/80
36 Brad Penny R/R 6-4 260 05/24/78
56 Ramon Ramirez R/R 5-11 190 08/31/81
24 Takashi Saito L/R 6-2 215 02/14/70
29 John Smoltz R/R 6-3 220 05/15/67
86 Junichi Tazawa R/R 6-0 175 06/06/86
49 Tim Wakefield R/R 6-2 210 08/02/66
Catchers B/T Ht Wt DOB
3 Josh Bard S/R 6-3 225 03/30/78
80 Dusty Brown R/R 6-0 180 06/19/82
68 George Kottaras L/R 6-0 185 05/16/83
33 Jason Varitek S/R 6-2 230 04/11/72
81 Mark Wagner R/R 6-1 205 06/11/84
Infielders B/T Ht Wt DOB
* 76 Argenis Diaz R/R 5-11 155 02/12/87
25 Mike Lowell R/R 6-3 210 02/24/74
12 Jed Lowrie S/R 6-0 180 04/17/84
23 Julio Lugo R/R 6-1 175 11/16/75
15 Dustin Pedroia R/R 5-9 180 08/17/83
20 Kevin Youkilis R/R 6-1 220 03/15/79
Outfielders B/T Ht Wt DOB
5 Rocco Baldelli R/R 6-4 200 09/25/81
44 Jason Bay R/R 6-2 205 09/20/78
54 Chris Carter L/L 6-0 230 09/16/82
7 J.D. Drew L/R 6-1 200 11/20/75
46 Jacoby Ellsbury L/L 6-1 185 09/11/83
11 Mark Kotsay L/L 6-0 205 12/02/75
60 Jonathan Van Every L/L 6-1 190 11/27/79
Designated Hitters B/T Ht Wt DOB
34 David Ortiz L/L 6-4 230 11/18/75

* Not on Active Roster
** Not on 40-Man Roster
 
Hi,

First what's happening, the reason is that Excel interpret 6-5 as a date,
which is standard procedure for Excel.

One solution: assuming the heights are in column C use

=MONTH(C2)&"-"&DAY(C2)

and copy down. Convert to values and replace column C.
 
When I do a web query and click on the yellow arrow at top left it selects
all on the page.

In D180:H225 I get this.............small sample only..........hope it comes
through.

40-Man Roster
Pitchers B/T Ht Wt DOB
19 Josh Beckett R/R 6-5 220 05/15/80
64 Michael Bowden R/R 6-3 215 09/09/86
61 Clay Buchholz L/R 6-3 190 08/14/84
17 Manny Delcarmen R/R 6-2 205 02/16/82
73 Felix Doubront L/L 6-2 166 10/23/87
84 Miguel Gonzalez R/R 6-1 170 05/27/84
43 Devern Hansack R/R 6-2 185 02/05/78


Gord
 
Got it
Thank you very much


Gord Dibben said:
What I do with something like this is use Data>Import External Data>New
Web
Query

Enter the URL in the dialog and go.

Select the range to import by clicking on the yellow "x"

Go to Options and "disable Date recognition"

Now hit Import.

The Ht is not converted to dates.

Elsewise, copy the data from the site and paste into Notepad.

Save as a *.txt file and open that in Excel which gives you the Text
Import
Wizard where you set the column data format as Text.


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top