Excel 2007 : How to remove a constant from a column

[malv007]

I have a long Excel column that has a constant in every cell. Does anyone know a way on how to remove it.
Example: I have in the column
06/14/1978 12:00pm
04/12/1960 12:00pm
09/34/1980 12:00pm
How can I get rid of the 12:00pm that appears on every cell but keeping the rest of the data

Blessings and thank you

[tom1977]

Hi select your column press CTRL + H and type 12:00pm into first box and second leave empty

[Pete_UK]

If they are proper dates and times, then put 0.5 in a cell somewhere. Select that cell and click <copy>. Then highlight all the cells that you want this to apply to and right-click then select Paste Special. In the dialogue box click on Values and on Subtract, then click OK and press the <Esc> key. Then you can remove the 0.5 from the original cell.

Note: 0.5 is equivalent to 12 hours in the way that Excel stores times.

Hope this helps.

Pete

[malv007]

Thank you for your reply
I did that, then selected "find all" then "replace all" then it shows that "Excel has completed its search and made 600 replacements". However, the 12:00pm remains.
Was that what I was supposed to do?
Thx again

[Paul]

Hi Malv, welcome to the forum.

You may want to post a copy of your workbook (less any confidential data) so we can see exactly how the data is formatted in the cells.

[malv007]

Thank you Pete
But I need to get rid of the entire time value constant and just keep the date.

[malv007]

You are right thank you.

[shg]

It says 12AM, not 12PM.

To make the time not appear in the cells, change the column format to mm/dd/yyyy

[malv007]

Thank you shg but the 12:00 is still there.

[shg]

It's where, in the cells or in the formula bar?

[malv007]

In the cells

[shg]

I changed the format of the column on your workbook as I described in my post, and the time was no longer displayed.

What did you do?

[malv007]

Thank you again.
I am attaching a screen shot of the process I am doing to see if I am missing something.

[shg]

I didn't suggest text to columns -- just select the column and format as mm/dd/yyyy

[malv007]

Thank you shg. Solved!

[shg]

You're welcome, good luck.