Moving a part of a cell to another cell

[Linda RQ]

Hi Everyone,

I am using Excel 2003. I copied some data from a report into excel. The
record took up 2 rows. For example, record 1 is taking up cell A1 thru F1
but the date and time for the record is in cell D1 and D2. I would either
like to combine the date and time into cell D1 and format it to Date and
Time or create a new column and move D2 into the new column.

How can I do this?

Thanks

 

[Pete_UK]

You can insert a new column E and in E1 put in this formula:

=D1 + D2

assuming these are in Excel date and time formats. Use Format | Cells
to give the cell a Custom format. If this is the only record, then
select E1 and click <copy>, then Edit | Paste Special | Values (check)
| OK then <Esc>. Then you can delete column D.

If you have more than one record then you can copy the formula in E1
down to cover the records that you have, and then fix the values in
column E as described above. Delete column D, then sort the records so
that the blank rows (except fot the value in column D) will drop to
the bottom, where you can easily delete them.

Hope this helps.

Pete

 

[Don Guillett]

Probably best to keep separate for future filtering.
This should do it.
If you do NOT want to delete the row with the time then delete the
rows(i).delete line

Sub movecellupandover()
Columns("e").Insert

For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -2
Cells(i, "d").Cut Cells(i - 1, "e")
Rows(i).delete
Next i
End Sub
A B C D E F
1 2 3 1-Jan 10:32 5 6
1 2 3 2-Jan 11:32 5 6
1 2 3 3-Jan 12:32 5 6
1 2 3 4-Jan 13:32 5 6
1 2 3 5-Jan 14:32 5 6

 

[Linda RQ]

Nice and easy. Thanks!
You can insert a new column E and in E1 put in this formula:

=D1 + D2

assuming these are in Excel date and time formats. Use Format | Cells
to give the cell a Custom format. If this is the only record, then
select E1 and click <copy>, then Edit | Paste Special | Values (check)
| OK then <Esc>. Then you can delete column D.

If you have more than one record then you can copy the formula in E1
down to cover the records that you have, and then fix the values in
column E as described above. Delete column D, then sort the records so
that the blank rows (except fot the value in column D) will drop to
the bottom, where you can easily delete them.

Hope this helps.

Pete

 

[Linda RQ]

Thanks Don...this looks advanced. Pete's works but I want to try yours too
when I have a little more time

Linda

 

[Pete_UK]

You're welcome, Linda - thanks for feeding back.

Pete