Hi,

I have the following data "Sep 8 2005 12:00:00:000AM" and need it
splitting down so that only the sep 8 2005 is in the cell. Formatting
the cell will not work (unless I am doing something wrong).

Please help - thank you in advance


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=469913

Is your entry a 'text' entry or a numeric entry with special formatting
to display MDY HH:MM:SS:nnn AM/PM?

more info, please


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469913

If you are not sure if it is text or numeric, format the cell as
General. What is displayed?

If it is 38603, you have a date value in the cell and is probably
formatted as Custom: mmm d yyyy h:mm:ss:ss AM/PM

If is still shows Sep 8 2005 12:00:00:000AM you have a text entry. (are
there really 3 zeros just before the AM?)

What do you find?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469913

The data appears to be a text entry (General in format cells).


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=469913

I would use Text to Columns for this. Highlight the cell, click
DataText to Columns...

In Step 1 of the wizard, choose "Fixed Width", click Next

Step 2 dble click the arrows after the 'p' and the '8' to delete,
click Next

Step 3 highlight the area to the right of '2005' and in the Column
Data Format box, click 'Do not import column (skip)'. Set the
Destination to the desired location (can be same cell if desired).
click Finish.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469913

moglione1 Wrote:
The data appears to be a text entry (General in format cells).


Try this:

=--LEFT(D7,6)
then format the cell as desired.
oops "D7" could be your cell of choice where you want to put it.

Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=469913