separate the contents of multiple cells 3

[eHanSolo]

Hi there,

I have a date and time in a cell which i'd like to separate in to two cells. Can anyone help me please?

So each cell would have: 17/03/2004 13:30:00

So the two is separated by a space...complicate things??

Anyway...any help is most appreciated!

 

[Molby]

The code below should work, just have the cell with the value in selected.

Sub split()
bob$ = ActiveCell.Value
ActiveCell.Offset(0, 1).Value = Left(bob$, InStr(bob$, " ") - 1)
ActiveCell.Offset(0, 2).Value = Right(bob$, Len(bob$) - InStr(bob$, " "))
End Sub

 

[Nikita6003]

Hiya

should the contents of the cell be numeric, use [tt]=INT(<CellAddress>[/tt] to get the date, and [tt]=<CellAddress> - INT(<CellAddress>)[/tt] to get the time

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[Molby]

The other thing to use which I originally overlooked is the Text to Columns, with Space delimited which will do the process much faster.

 

[TonyJollans]

Hi eHanSolo,

If you have Date/Time value you can take it's constituent parts ..

In A1 you have date/time value [purple]17/03/2004 13:30:00[/purple]
In B1 you enter [blue]=TEXT(A1,"dd/mm/yyyy")[/blue] giving 17/03/2004
In C1 you enter [blue]=TEXT(A1,"hh:mm:ss")[/blue] giving 13:30:00

If, on the other hand, you have a text string you will need to split it at the space(s) as has already been shown (the only thing I would add would be to TRIM the time) .. BUT, the string you posted actually contains Chr(160) characters and not spaces. Is this just a quirk of posting or does it represent your data?



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[eHanSolo]

hey, thank you so much for all your help guys and gals!!!

^_^