Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Split cell without breaking word? | Excel Discussion (Misc queries) | |||
How do I keep the zeros in front of numbers when i split a cell | Excel Worksheet Functions |