#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Split a Cell?


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Split a Cell?


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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Split a Cell?

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" wrote in
message ...

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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 273
Default Split a Cell?

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Split a Cell?

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

Biff

"Biff" wrote in message
...
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" wrote
in message ...

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







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Split a Cell?


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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Split a Cell?

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" wrote in
message ...

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert date Larry Excel Worksheet Functions 28 July 15th 06 02:41 AM
Compiling macro based on cell values simonsmith Excel Discussion (Misc queries) 1 May 16th 06 08:31 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
Split cell without breaking word? Doug Benjamin Excel Discussion (Misc queries) 8 April 13th 06 01:23 AM
How do I keep the zeros in front of numbers when i split a cell tom Excel Worksheet Functions 3 October 11th 05 03:21 AM


All times are GMT +1. The time now is 09:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"