Splitting content of cell into 2 cells

  • Thread starter Thread starter Mark Christensen
  • Start date Start date
M

Mark Christensen

Hello,

I've got Excel 2002. I have a spreadsheet with 20,000+ rows. Column B of
each row contains a date and time stamp in this format:

mm/dd/yyyy hh:mm (Note that if any field is a single digit there is not a 0
place holder - i.e. March 1st would appear as 3/1/2006 and not 03/01/2006.
Same thing for the hours.)

Is there a way to automatically pull the hh:mm part out of column B and have
it put into Column C (which is empty)? Thanks.

Mark
 
Highlight column B.

Select Data menu, Text to Columns command.

Wizard should take you the rest of the way through (choose delimited on 1st
screen, check off "space" in second screen.
 
Assuming that the data in Column B are *true* XL recognized dates, simply
copy the cell over to Column C using:

=B1

And copy down as needed.

THEN ... simply format Column C to:

hh:mm
 
OK I did this and it worked except that when I click on a cell in column B
the time stamp is still displaying in the formula bar even though it's not
displaying in the actual cell. How do I get rid of it altogether now that I
have the time in column C? Thanks again!
 
Hi there,

If you are happy with the contents of olumn C and want to delete Column B
then you will need to take two steps.

1. Remove the dependency from C on B
2. Delete Column B.

For the first step, select the entire Column C and go to Edit-->Copy. Then,
with Column C still selected, go to Edit-->Paste Special. Under the "Paste"
section, select Values and press OK. This will replace all of the =B1, =B2
etc with the actual values.

For the second step, select the entire Column B and go to Edit-->Delete.

HTH,
Katherine
 
If you want just the date in column B and the time in column C, then
insert a new column C and enter these formulae:

C1: =INT(B1)
D1: =MOD(B1,1)

Format the cells as you would like them, then copy the formulae down.
Fix the values in columns C and D (highlight them, click <copy> then
Edit | Paste Special | Values (check) then OK followed by <Esc>. You
can now delete the original data in column B.

Hope this helps.

Pete
 
Back
Top