copy paste from html to excel

rakic

New Member
Joined
Oct 11, 2002
Messages
5
Hi !

Usually results of Football (Soccer) matches are given like 3:1, 2:2, 0:1. See picture“ORIGINAL”
ORIGINAL
12. kolo GERMANY 1
Dan Cas R.b. 45' 90'
Sub 15:30 5201 Bayern M. Cottbus 1:1 3:1
Sub 15:30 5202 S Hannover Bochum 1:1 2:2
Sub 15:30 5203 S Hansa Dortmund 0:1 0:1
Sub 15:30 5204 S Hertha Hamburger 0:0 2:0
Sub 15:30 5205 S K'lautern München 1860 0:0 0:0



….When I want to copy these results and after that to paste them into EXCEL, they are changed. After the paste they become 3:01, 2:02, 0:01……… and so on.
See picture “RESULT OF PASTING IN EXCEL”
RESULT OF PASTING IN EXCEL
12. kolo GERMANY 1
Dan Cas R.b. 45' 90'
Sub 15:30 5201 Bayern M. Cottbus 1:01 3:01
Sub 15:30 5202 S Hannover Bochum 1:01 2:02
Sub 15:30 5203 S Hansa Dortmund 0:01 0:01
Sub 15:30 5204 S Hertha Hamburger 0:00 2:00
Sub 15:30 5205 S K'lautern München 1860 0:00 0:00


Is there a way to prevent this change of format to TIME.
The best thing would be to place result in two columns like :

Bayern M. Cottbus 3 1
Hannover Bochum 2 2
Hansa Dortmund 0 1
If it is possible.

Thank you in advance

DORDE
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Some guesses on my part:

Select All, Format the whole page as Text.

I would think PasteSpecial-Values. Pasting HTML takes Forever.

Now, did the scores change? If not, then stop right here.

If then did change, let us know, and we will think more on this challenge.

SteveBausch
 

rakic

New Member
Joined
Oct 11, 2002
Messages
5
Thank You Steve,
but on my pity there is no positive results. Paste in EXCEL show me only time. If you allow me I can send you a capture (grab)from original site, or perhapes address is : http://www.zona.org.yu
but it is in Serbien.
Thank You and to alls

Dorde
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
look at webqueries for this, there is an advanced option to ignore time and date formatting.

HTH

Chris
 

rakic

New Member
Joined
Oct 11, 2002
Messages
5

ADVERTISEMENT

Thank You Chris !
I've test your sugestion but results are not so good. There is an option "DISABLE DATE RECOGNITION" but not TIME RECOGNITION.
I'll do it some more, perhaps....

Dorde
 

reged

New Member
Joined
Oct 12, 2002
Messages
4
I have a similar problem - only mine is to do with the minus sign - and the only way I can solve it is to download the page first into Word, run a macro to change the character (like - or :) to something excel wont recognise, import this saved file into excel with a query and then use excel functions to convert it back to a text entry in whatever format you want. Longwinded, but it works.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

Okay, one more attempt on my part, to correctly understand, and then remedy, this situation.

I think you want the paste to come over in columns, right?

If you aren't worried about having in seperate columns, you can first paste it into NotePad, SelectAll, Copy, and then paste it into Excel

You lose the columns, but you keep the formatting.
This message was edited by stevebausch on 2002-10-13 20:13
 

reged

New Member
Joined
Oct 12, 2002
Messages
4
I don't think that's it Steve.

I take his problem to be that as soon as excel sees the colon between the scores it decides this is a time value and reinterprets it as such. So instead of getting something like 1:1 he gets 1:01 which is a bit silly.

As I said I have a similar problem with horse-racing stats, where the hyphen is used to separate seasons in stats on previous performances. It works OK except where the string of figures starts with a hyphen and has another in the middle, whereupon it decides its a formula and subtracts one from the other. That's why I go through Word first (and also so I can keep preceding zeroes which don't mean zero at all but a really crap horse!)
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
If you want to get around tricky HTML formatting problems which the standard webquery wont do you can use the xmlhttp object. You will have complete control over the page passed back to you BUT you will have to sift through all the html crap to to your data.

Take a look at the code below which gets information from the yahoo stocks site.

Hope this helps.

Chris

Sub GetShareInfo()

Dim xmlhttp

'Create the XML document type
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

'Get my range of the stock quotes
Set rng = Range("A1:A10")

For Each c In rng

'Have we got the last quote
If IsEmpty(c.Value) Then Exit For

'Open a connection to the server and get the share price
strURL = "http://finance.yahoo.com/q?s=" & Trim(c.Value) & "&d=v1"

'get the strURL
xmlhttp.Open "GET", strURL, False, "", ""

'send the information
xmlhttp.Send

RtnPage = xmlhttp.ResponseText

If InStr(1, RtnPage, "No such ticker symbol") Then
c.Offset(0, 1).Value = "No Such Symbol"
Else
'Find the start of the quote
QuoteStart = InStr(RtnPage, Trim(c.Value) & "</a>")

'Find the second cell in which is where the actual quote is
FirstCellStart = InStr(QuoteStart, RtnPage, "<td")
SecondCellStart = InStr(FirstCellStart + 1, RtnPage, "<td")

'We now have the information required in the 3rd cell - the quote
'The actual quote is between the and tags, so get these positions
'and hence the quote!

FirstTag = InStr(SecondCellStart, RtnPage, "")
SecondTag = InStr(FirstTag, RtnPage, "
")
Quote = Mid(RtnPage, FirstTag + 3, (SecondTag - FirstTag) - 4)

c.Offset(0, 1).Value = Quote
End If

Next

End Sub
 

reged

New Member
Joined
Oct 12, 2002
Messages
4
Chris, slightly off topic but maybe you can help with this.

Is there any way of getting a simple excel query to obtain its URL from an active worksheet cell?
 

Forum statistics

Threads
1,144,274
Messages
5,723,444
Members
422,497
Latest member
dougy99

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