Split a Cell?

[inveni0]

I have a column "A" with data like the following:

6/12/2006 9:30

I would like to split this into two other columns like:

COLUMN B | COLUMN C
6/12/2006 | 9:30


Is there a formula that can split the cell data at the "SPACE" between the date and time?

[VBA Noob]

Have you tried

Data > Text to Columns. Delimiter will be space.

VBA Noob

[Biff]

>6/12/2006 9:30

If the entry is a real Excel DATE/TIME there is no space!

There would only be a space if the entry was TEXT.

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Have you tried
>
> Data > Text to Columns. Delimiter will be space.
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=562643
>

[Gary]

Inveni0,

I do it this way (clumsy maybe):

In column B, I enter either rounddown(A1,0) or int(A1)

In column C, I enter B1-A1

I then copy the formulas to values, delete column A, and then format column
B to show date and column C to show time.

Hope this helps.

"inveni0" wrote:

>
> I have a column "A" with data like the following:
>
> 6/12/2006 9:30
>
> I would like to split this into two other columns like:
>
> COLUMN B | COLUMN C
> 6/12/2006 | 9:30
>
>
> Is there a formula that can split the cell data at the "SPACE" between
> the date and time?
>
>
> --
> inveni0
> ------------------------------------------------------------------------
> inveni0's Profile: http://www.excelforum.com/member.php...o&userid=31178
> View this thread: http://www.excelforum.com/showthread...hreadid=562643
>
>

[Biff]

Well, technically, T to C will parse the data if it is a DATE/TIME but look
at how it ends up!

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> >6/12/2006 9:30
>
> If the entry is a real Excel DATE/TIME there is no space!
>
> There would only be a space if the entry was TEXT.
>
> Biff
>
> "VBA Noob" <[email protected]> wrote
> in message news:[email protected]...
>>
>> Have you tried
>>
>> Data > Text to Columns. Delimiter will be space.
>>
>> VBA Noob
>>
>>
>> --
>> VBA Noob
>> ------------------------------------------------------------------------
>> VBA Noob's Profile:
>> http://www.excelforum.com/member.php...o&userid=33833
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=562643
>>
>
>

[inveni0]

Have you tried

Data > Text to Columns. Delimiter will be space.

VBA Noob

Works like a charm! Thanks, man!

[Ragdyer]

There's really *no* need for *any* formulas - *except* for the one to copy
the data to the adjoining column:

If A1 contains "6/12/2006 9:30" (no quotes)
Then in B1 enter
=A1

And the rest is accomplished with simple formatting!

A1 = m/d/yyyy
And
B1 = h:mm

And you're done!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"inveni0" <[email protected]> wrote in
message news:[email protected]...
>
> VBA Noob Wrote:
>> Have you tried
>>
>> Data > Text to Columns. Delimiter will be space.
>>
>> VBA Noob
>
>
> Works like a charm! Thanks, man!
>
>
> --
> inveni0
> ------------------------------------------------------------------------
> inveni0's Profile:
> http://www.excelforum.com/member.php...o&userid=31178
> View this thread: http://www.excelforum.com/showthread...hreadid=562643
>