Help: Auto applying a formula to new data when entered in to a specific column.

I am new to the complex excel formula's but I have found a formula that is working very well for me but when I paste in new data to my sheet I have to use the drag option to fill the command down the column. I wouldn't generally care but I am pasting generally 300-400 items every 3 days. Here is the formula (=LEFT(SUBSTITUTE(SUBSTITUTE(D4,"-"," ",3),".",":"),16)*1). The D4 is the column to the left and E is the column that formula is actually in. It changes as you drag down (D5, D6, etc) and it converts date's in this format (2014/11/21 08:47 EST) to this (11/21/2014) so that excel reads it as a date and then can be sorted as such in column E. so every time I paste new data in it will have (2014/11/21 08:47 EST) in column D and I would like excel to auto use the formula, when the paste occurs,(=LEFT(SUBSTITUTE(SUBSTITUTE(XX"-"," ",3),".",":"),16)*1) in column E. Any and all help is much appreciated. (example layout below)

D	E
2014/11/21 07:21 EST	11/21/2014
2014/11/21 07:21 EST	11/21/2014
2014/11/21 07:21 EST	11/21/2014
2014/11/22 12:27 EST	11/22/2014

Thanks!

Thanks! I was totally unaware that I could double-click the corner. I have always been taught to drag it. Much appreciated.

I will try the table idea too.

Originally Posted by FDibbins

This will do the same thing, but is a bit shorter...
=DATEVALUE(LEFT(D4,10))

The table idea is a good 1, and is probably the better way to go.

However, you you really want to just use your formulas, you could use this, copied wayyyy past where your data ends...
=IF(D5="","",DATEVALUE(LEFT(D5,10)))

If there is nothing in D, it wont show anything, when data is pasted into D, the formula will calc and show

So thanks to the above DATEVALUE here's what I was able to come up with, I decided to keep the time as well and now have a formula that will show both the date and time extracted from the cell.

=(DATEVALUE(LEFT(D1,10)))+(TIMEVALUE(MID(D1,12,5))) will return 11/14/14 13:37. Then I just double-click the corner and the formula applies all the way down. As long as the cell is set correctly when formatting (date> 3/14/01 13:30) it works flawlessly. Thanks again!