Can't download from web

[plato]

I have been unable to download data satisfactorily from a web site using web query. I can download the info from the web site by copying and pasting, however excel does not recognise the numbers that I have cut and pasted for use in formulas. When I place numbers in other columns Excel formulas will work but not the numbers that I have cut and pasted.
It appears to be something to do with the web site construction.
I first go to this web site and then click on customized race guide and this allows you to select the info you require. Then when clicking display it takes you to the final page which I would like to import directly into Excel.
I have tried as an experiment to go direct to the 2nd and 3rd site using web query nothing appears.

http://www.racingandsports.com.au/ne...?meeting=11001

http://www.racingandsports.com.au/ne...eetingID=11001


http://www.racingandsports.com.au/ne...on=displaylong

Hoping someone can provide some assistance please..
Gil

[martindwilson]

cant help with web query but select column with text numbers in then data/ text to columns/next/next finish. will convert them to real numbers

[plato]

Hello,Thank you for response.I have tried this but without success.After attempting to convert I get a message #Value? in the cell.When I look at the calculating stages this is what it shows under evaluation ="100"+"8".

[martindwilson]

problem isi cant get to those pages to have a look!

[plato]

Hello,
I realise now that the links are of no value to anyone trying to assist as the bottom two were the ones I cannot reach with the web query.
The following is how I arrive at the sites from the original link

http://www.racingandsports.com.au/newform/

From there select a meeting highlighted in blue and click which takes to the next page.About half way down on this page
there is on the right there is a button “Customised race guide”.
Clicking on this takes you to the page where I select what I need on the guide.
Normally I would leave Field format selected and select Tab.No,Horse,barrier and weight.
From the other columns I select Days since last win and Runs since last win and W%P% career.
After selecting these I click display race guide down the bottom of page,and then display on the next page.

This takes me to the data I wish to download in Excel and have excel recognise the numbers so that I can do calculations and If statements =<,>= etc.
As stated I have copied and pasted but Excel does not seem to recognise numbers as numbers from this info.

[martindwilson]

ok i only did this for one race
paste into excel


i then inserted 2 new columns by selecting h7:h16 /insert/shift columns to right

selected data in g7:g16 and did data text to columns delimited chose - as delimiter -finish
then typed a 0 into a1 copied it
then selected d7:j16 paste special /add
then select f7:f16 format cells %
you might want to unmerge all cells first (select whole sheet,format cells allignment click untill merge cells checkbox is clear) then you could do all the races at once

[plato]

Hello, Thank you .With your help I am getting very close to an answer. I have attached the same info downloaded below on the worksheet attached.
The column days last win (DLW) and (RLW) needs to be a numerical. I see you have been able to separate the three career figures which are great. The number on the left of career needs to be % and on the right needs to be a numerical. There appears to be some difference in the columns RLW is now the column on the far right?
For me to work with an if statement such as- If F7>1, the columns with numbers would have to be in number form suitable for working with Excel formulas. I have tried to use some formulas in the attached but receive error message Value?
I suppose the % column doesn’t matter if it is in % or number as all I need to check using Excel formula if the % is greater than 16.
Thanks again for your assistance.
Gil

[martindwilson]

just to be a nuisance its got loads of non breaking spaces
to fix that
after you seperate out the %-%-x vcols
select the whole sheet
use find replace
find... hold down alt key and type 0160 (on the numeric keypad on the right of keyboard)
the cursor will move across what looks like a space
replace .......leave blank
replace all

[plato]

Hello Martin,
I would like to extend my thanks for the time you have taken to assist me in my project.You have provided me with the solution to my issues and will now make my workouts a very simple and automatic procedure.
Once again many thanks for your efforts.
Gil