inserting a column

[zebra7860]

Hello,

I managed to copy and paste some stock prices into Excel but I need to delete a few columns and the problem is that the format does not allow me to separate whats in one column.

For example, the data I have is as follows in 3 columns:

YYYY MM DD value | YYYY MM DD value| YYYY MM DD value

The YYYY MM DD is the same date on one line and the "value" are actual numbers but what I want to do is simply have one column that contains the date followed by value,value,value. How do I segregrate the YYYY MM DD so that I can delete the columns?


Any help would be appreciated,
Thanks,
Al

 

[santeria]

Can you give a visual example of the sheet?

 

[Greg Truby]

In columns 2 and 3 (do each separately) use Data | Text to Columns... then use Fixed Width. Cut just to the right of your DD column. Then tell Excel to Skip that date column and keep your right-half as one column of general.

That should do the trick. (Always back up before trying new stuff...)

 

[Smitty]

This will return the date only: =LEFT(A1,FIND(" ",A1)-1)
This will return the price only: =RIGHT(A1,LEN(A1)-FIND(" ",A1))

You can also use Data-->Text to Columns.

Hope that helps,

Smitty

 

[zebra7860]

Okay,

I want to give everybody an idea of what I am doing and I tried Greg's method although it worked but there are issues that still need to be revolved.

Here is the website: http://www.economagic.com/sp.htm

Note that I want the S&P DAILY format, so what you do is highlight and copy the date and the open, high, low and close prices and paste them into Excel and since the dates are repetitive, you remove them. The problem is that when I tried Greg's method, it leaves me with a messy number

I can't explain it more than that. Hopefully someone can step in here and try and figure out what to do.

Thanks,
Al

 

[zebra7860]

oops!!

The tData-->Text to Columns works well, and I overlooked the fact that I needed to remove more numbers to make it work.

Thanks to everybody!

Al

 

[olarge1]

This may sound stupid but have you tried selecting the display data in copy paste format ? as this puts the data in alt tab so u can simply paste it in to excel and it will automatically put it in the relevant 4 columns for you and then you can delete as necessasry!!


Hope this helps


Ed
:wink:

 

[Greg Truby]

Al,

It appears that they're embedding some type of hidden digit between the date and the index number. As long as the index number does not vary in the length of digits, Text to Columns should do you fine, just split to the left of the highest digit you want to keep. But if the index jumps a placeholder (999 to 1000) then that extra digit to the left of the first nine is going to wreak havoc. I'll think on this, in the meantime, maybe someone else will show up with a slick way on how to weed that out. Otherwise you'd need to add in some type of "common sense" check (adding a column showing % or absolute row-to-row changes or column-to-column changes and flagging for extraordinary values).

 

[Smitty]

It might be worth paying the Level I subscription fee that allows you to get the data in Excel format.

Smitty