finding and removing the time in a cell

[kugenanki]

Hi, im working on a file that has alot of data and all i need to do is to find and remove the time which includes the AM/PM (attached file is an example). what i do is to use the find and use a condition *:*00*AM and *:*00*PM which brings me the cells and then remove the time manually without removing the words "Dropdate: 08/12/2014". I just wonder if there's a way to do this quicker, using replace or maybe a formula. the important thing is to leave the words Dropdate with the date.

Thanks in advance!

[FDibbins]

maybe this...
=IF(ISNUMBER(SEARCH("Dropdate",D4,1)),LEFT(D4,20),"")

[kugenanki]

this formula works hands down, but if its possible something like how find/replace works so i can remove the time on its active cell. the LEFT function is perfect for this but i dont think you can use functions in the find/replace.

[FDibbins]

i dont think you can use functions in the find/replace.

That is correct.

Perhaps use my formula in a helper column, then when you are finished, copy/paste values over the original data and then delete the helper. Or, if this will be an on-going process, keep the helper and hide the column with the original data in?

[Vikas_Gautam]

Try this.. Drag down..

=IF(ISERR(FIND("Dropdate",D4,1)),"",LEFT(D4,20))

check the attached file..

[Vikas_Gautam]

I got the crack...
Try find and replacing.. without Quotes..("")
" ???????? AM"
and
" ???????? PM"
with "" (Nothing..)
it will do...
Remember it starts form SPACE....

[kugenanki]

Vikas_Gautam this one is what im looking for!!!! waaaahhhh!!!!!! thank you for taking time to check my file, thanks to FDibbins as well, YOU GUYS ARE THE BEST!!!