inserting a column

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
403
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Active Member
Joined
Jan 9, 2004
Messages
403

ADVERTISEMENT

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

Active Member
Joined
Jan 9, 2004
Messages
403
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

New Member
Joined
Oct 30, 2003
Messages
9

ADVERTISEMENT

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
:p :wink:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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

Legend
Joined
May 15, 2003
Messages
29,536
It might be worth paying the Level I subscription fee that allows you to get the data in Excel format.

Smitty
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,525
Members
425,480
Latest member
br400821

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