Divide a cell Content

[binetht]

Hi,

I have a cell which containing date and time as below,

06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00

I want to divided this cell in to 4 separate cells of date and time.

Thanks

 

[etaf]

Use
=LEFT(A1,19)*1
=MID(A1,20,19)*1
=MID(A1,39,19)*1
=RIGHT(A1,19)*1

The *1 is to change the text output to a date value, otherwise it will stay as TEXT

Book1
	A
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00
2
3	10/6/20 10:00
4	10/8/20 13:36
5	10/12/20 9:18
6	10/12/20 14:42
Sheet1

Cell Formulas
Range	Formula
A3	A3	=LEFT(A1,19)*1
A4	A4	=MID(A1,20,19)*1
A5	A5	=MID(A1,39,19)*1
A6	A6	=RIGHT(A1,19)*1

 

[binetht]

Use
=LEFT(A1,19)*1
=MID(A1,20,19)*1
=MID(A1,39,19)*1
=RIGHT(A1,19)*1

The *1 is to change the text output to a date value, otherwise it will stay as TEXT

Book1
	A
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00
2
3	10/6/20 10:00
4	10/8/20 13:36
5	10/12/20 9:18
6	10/12/20 14:42
Sheet1

Cell Formulas
Range	Formula
A3	A3	=LEFT(A1,19)*1
A4	A4	=MID(A1,20,19)*1
A5	A5	=MID(A1,39,19)*1
A6	A6	=RIGHT(A1,19)*1

Thak you!

 

[etaf]

you are welcome

 

[Peter_SSs]

Formula option is fine but you could also have used Text to Columns (either manually or by macro) with Fixed Width of 19 characters.

Result automatically formatted (for me anyway)

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	6/10/2020 10:00	8/10/2020 13:36	12/10/2020 9:18	12/10/2020 14:42
Split Date & Time (2)

 

[binetht]

Formula option is fine but you could also have used Text to Columns (either manually or by macro) with Fixed Width of 19 characters.

View attachment 28542

Result automatically formatted (for me anyway)

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	6/10/2020 10:00	8/10/2020 13:36	12/10/2020 9:18	12/10/2020 14:42
Split Date & Time (2)

Thanks.

 

[Peter_SSs]

Thanks.

You're welcome.

BTW, if you are preferring a formula solution, and assuming 4 date/time values per cell here is another option. It only needs to be entered in column B and all 4 results should automatically 'spill' across the 4 result columns.

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	06/10/2020 10:00:00	08/10/2020 13:36:00	12/10/2020 09:18:00	12/10/2020 14:42:00
Split Date & Time (3)

Cell Formulas
Range	Formula
B1:E1	B1	=--MID(A1,SEQUENCE(,4,,19),19)
Dynamic array formulas.

.. and if you actually have a column of such values you can get all 4 results for all rows at once with this formula in the top left result cell only.

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	06/10/2020 10:00:00	08/10/2020 13:36:00	12/10/2020 09:18:00	12/10/2020 14:42:00
2	26-10-2020 08:00:0008-10-2020 13:36:0019-10-2019 09:14:0012-10-2020 14:42:00	26/10/2020 08:00:00	08/10/2020 13:36:00	19/10/2019 09:14:00	12/10/2020 14:42:00
3	01-11-2020 08:15:0008-10-2020 13:01:0012-03-2020 09:18:0012-12-2020 14:59:00	01/11/2020 08:15:00	08/10/2020 13:01:00	12/03/2020 09:18:00	12/12/2020 14:59:00
Split Date & Time (4)

Cell Formulas
Range	Formula
B1:E3	B1	=--MID(A1:A3,SEQUENCE(,4,,19),19)
Dynamic array formulas.

 

[binetht]

You're welcome.

BTW, if you are preferring a formula solution, and assuming 4 date/time values per cell here is another option. It only needs to be entered in column B and all 4 results should automatically 'spill' across the 4 result columns.

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	06/10/2020 10:00:00	08/10/2020 13:36:00	12/10/2020 09:18:00	12/10/2020 14:42:00
Split Date & Time (3)

Cell Formulas
Range	Formula
B1:E1	B1	=--MID(A1,SEQUENCE(,4,,19),19)
Dynamic array formulas.

.. and if you actually have a column of such values you can get all 4 results for all rows at once with this formula in the top left result cell only.

20 12 25.xlsm
	A	B	C	D	E
1	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	06/10/2020 10:00:00	08/10/2020 13:36:00	12/10/2020 09:18:00	12/10/2020 14:42:00
2	26-10-2020 08:00:0008-10-2020 13:36:0019-10-2019 09:14:0012-10-2020 14:42:00	26/10/2020 08:00:00	08/10/2020 13:36:00	19/10/2019 09:14:00	12/10/2020 14:42:00
3	01-11-2020 08:15:0008-10-2020 13:01:0012-03-2020 09:18:0012-12-2020 14:59:00	01/11/2020 08:15:00	08/10/2020 13:01:00	12/03/2020 09:18:00	12/12/2020 14:59:00
Split Date & Time (4)

Cell Formulas
Range	Formula
B1:E3	B1	=--MID(A1:A3,SEQUENCE(,4,,19),19)
Dynamic array formulas.

Thank you, I was going to ask that question and u answered it before I asked. hehe :D thankyou very much!

and I have another issue, once I split the dates in to cells, those are visible as (Text) Strings, not Date type cells. so What Can I do to make it date and time format.

 

[Peter_SSs]

those are visible as (Text) Strings,

My formula actually returns Dates (numbers) not text strings. That is what the "--" just after the equals sign does.
You may need to select the result columns and format them to your preferred Date/Time format. For my mini-sheets above, i used this format:

 

[binetht]

Thank you, I was going to ask that question and u answered it before I asked. hehe :D thankyou very much!

and I have another issue, once I split the dates in to cells, those are visible as (Text) Strings, not Date type cells. so What Can I do to make it date and time format.

My formula actually returns Dates (numbers) not text strings. That is what the "--" just after the equals sign does.
You may need to select the result columns and format them to your preferred Date/Time format. For my mini-sheets above, i used this format:

View attachment 28755

Ok Thanks You very very Much