putting data into excel

northernsoul

I have some shares and when looking on the website the table is, as you would expect, in rows and columns. Each row consists of several columns, eg company name , price , price change , gain loss etc and each row a different company.
Problem is when i download and save into excel there is no seperation between the columns in each row. All the data for the company, the price , price change , gain loss etc are put into 1 column ie all data for ITV in A1 , for Pru in A2 , for Rentokill A3 etc. This makes it difficult to read.
Inbetween all data there are these 3 symbols ","
ie RTO","1392","RENTOKIL INITIAL","83.55","1.75"
Is there any way to automatically use these symbols to seperate the data so that each goes in its own seperate column in excel?

googler

Copy/paste the data into a text file, save it, and if it's in the format you describe, with commas separating each field, choose the Import External Data from the ... Data menu, and select comma as the separator.

Don't paste it directly into Excel from the webpage.

garynuman

Each data field (or column) is usually appended with a delimitter such as "," or " " for it to be imported into Excel.

Your problem is that the data itself may contain " " characters so will fail. With no usuable delimitter it will not be easy to import. The import "wizard" in Excel will be of little help.

If there's only a small amount of data, it may be easier loading it up in Notepad and inserting a "," between each data field. Once saved, this can be imported into Excel.

garynuman

Sorry Googler - you beat me to it!

Figment

northernsoul wrote: »

I have some shares and when looking on the website the table is, as you would expect, in rows and columns. Each row consists of several columns, eg company name , price , price change , gain loss etc and each row a different company.
Problem is when i download and save into excel there is no seperation between the columns in each row. All the data for the company, the price , price change , gain loss etc are put into 1 column ie all data for ITV in A1 , for Pru in A2 , for Rentokill A3 etc. This makes it difficult to read.
Inbetween all data there are these 3 symbols ","
ie RTO","1392","RENTOKIL INITIAL","83.55","1.75"
Is there any way to automatically use these symbols to seperate the data so that each goes in its own seperate column in excel?

Download and save as a .CSV file (Comma Separated Values). Open the file with Excel and the values should all be in their own cell.

northernsoul

On the webpage it gives a tab saying 'export to excel, when i do this it does put it into excel as a csv file but as i said, the data not in their own individual cell.
On the web page within all the data values it has data whereby if you click on them it opens up as a graph or another one as history of the company. there are about 4 of these and i cannot copy the text and numbers without copying these.
i copied into a word doc and also notepad. In word not everything i copied showed up on the word doc. During importing from Word it said i had a comma seperated file but after going through it came out as gobaldy gouk ie like programming language. From notebook it did not come out seperated but was something like but similar to before.
Thanks for your help but i think this is beyond me

garynuman

Just a thought, it may be worth trying to import as "Tab Delimited" - provided the spacing of the source file columns are consistant.

going through it came out as gobaldy gouk ie like programming language.

XML Maybe?

bob_a_builder

It shouldn't be ...

as other have said,

do the export from web as a .CSV

THEN
open it up using Notepad ( NOT excel)
each row should look like
"field_1" , "Field_2" etc
on a separate line

I have some .CSV files like this i.e no quotes, and just clicking on them if named as .CSV they open up OK in Excel withOUT using import - just normal open
qqqqq,wwwww,eeeeee,rrrrrrrrr,tttttt

Try making such a file using Notepad ( save it as xxxxx.CSV ) and then import that to Excel as a CSV - once you have got the hang of that you can then fiddle the other data to match

Figment

northernsoul wrote: »

On the webpage it gives a tab saying 'export to excel, when i do this it does put it into excel as a csv file but as i said, the data not in their own individual cell.
On the web page within all the data values it has data whereby if you click on them it opens up as a graph or another one as history of the company. there are about 4 of these and i cannot copy the text and numbers without copying these.
i copied into a word doc and also notepad. In word not everything i copied showed up on the word doc. During importing from Word it said i had a comma seperated file but after going through it came out as gobaldy gouk ie like programming language. From notebook it did not come out seperated but was something like but similar to before.
Thanks for your help but i think this is beyond me

Is it exporting directly into Excel, or saving as a CSV file first?

The sample data you gave in the OP is in CSV format, although there should be a " as the first character. Was this a typo on your part or is the imported data missing that character?

Dave_C_2

northernsoul wrote: »

I have some shares and when looking on the website the table is, as you would expect, in rows and columns. Each row consists of several columns, eg company name , price , price change , gain loss etc and each row a different company.
Problem is when i download and save into excel there is no seperation between the columns in each row. All the data for the company, the price , price change , gain loss etc are put into 1 column ie all data for ITV in A1 , for Pru in A2 , for Rentokill A3 etc. This makes it difficult to read.
Inbetween all data there are these 3 symbols ","
ie RTO","1392","RENTOKIL INITIAL","83.55","1.75"
Is there any way to automatically use these symbols to seperate the data so that each goes in its own seperate column in excel?

Excel will do this for you, everyone else is missing this (instructions for Excel 2003)

1. Highlight the text you have just saved into Excel
2. Now select Data > Text to columns
3. The wizard should identify that it's delimited, so click next
4. On the next page check comma and make sure that " (double quotes) is in text qualifier
5. Select Next
6. Make any format changes to the columns then Select Finish
7. Job done

Hope this helps,

Dave

northernsoul

Hi.
THankyou everybody for your further suggestions. Will try again tomorrow.
It imports the data directly into Excel as a comma seperated values file, originally into the downloads folder when i clicked save, but now i save as and it puts it in my finance folder

Thanks