Split DateTime string, ArrayFormula, Google Spreadsheet

0
votes

I have a 'Date' 'at' 'Time' string in A1, for example:

A1: December 27, 2013 at 08:24AM

I would like to have Date and Time in B1 and C1, for example:

B1: 12/27/2013 C1: 8:24:00

How to split the A1 into B1 and C1? And what if I have a new row like

A2: January 02, 2014 at 08:28AM

then

B2: 01/02/2014 C2: 08:28:00

can be generated automatically?

It seems I can achieve this via "ArrayFormula" and "REGEXEXTRACT", but after lots of search, I still can not find the formula to apply to my spreadsheet.

2
google-sheets
Why do you create an account over at Web Applications, and collect your rep: webapps.stackexchange.com/q/54645/29140 - Jacob Jan Tuinstra
@JacobJanTuinstra It is not done by me, this question should be migrated by the stack overflow web administrator. - srjohnhuang
@pnuts no, it should be without ',' - srjohnhuang

2 Answers

4
votes

One way; in B1:

=ArrayFormula(IF(LEN(A:A),REGEXREPLACE(A:A,"(.*?) at (.*)",{"$1","$2"})+0,))

and then format the B column as Date and C column as Time.

0
votes

Simpler to select the two cells, Data > Split text into columns... with Custom Separator of at then format to suit.