CODEWITHSUNDEEP
excelgoogle-sheets
Paulo Duarte
Paulo Duarte

Reputation: 93

Extract second, third and fourth word of a cell in a googlesheet

Good morning, mates!

I have a google spreadsheet generatin activities informations, like the following below, where the phrase is inserted in one cell per row:

April 19, 2018 at 12:07AM
April 19, 2018 at 12:07AM
April 19, 2018 at 12:15AM
April 19, 2018 at 12:15AM
April 19, 2018 at 11:43AM
April 19, 2018 at 11:43AM

And this rows are constantly updating with new activities informations, reporting activities related to time, month, year and day.

I would like to split that information into 4 cells, instead of just one.

[Month][Day][Year][Time] <---- each one in a separated cell.

To do that, i think that i would need a formula to extract the month of the phrase to one new cell, then would need to take the Day for another cell, then the year and then the time. But i have no clue on how to do that. Could you guys help me?

Thanks a lot!

Upvotes: 0

Views: 5818

Answers (4)

Max Makhrov
Max Makhrov

Reputation: 18717

=SPLIT(REGEXREPLACE(A14,"(.*),(.*)at (.*)","$1$2$3")," ")

Upvotes: 1

TheMaster
TheMaster

Reputation: 50732

=ARRAYFORMULA(REGEXEXTRACT(A2:A7,"^(\w+)\s+(\d+),\s+(\d{4})\s+at\s+(.+)$"))
  • \w word
  • \d digit
  • \s space

Upvotes: 0

user4039065
user4039065

Reputation:

You can convert to a true datetime with,

=--SUBSTITUTE($A1, " at ", " ")
  1. Show April in the cell with a cell number format of mmmm.
  2. Show 4 in the cell with a cell number format of m.
  3. Put 4 in the cell with =MONTH(--SUBSTITUTE($A1, " at ", " ")).
  4. Put the string April in the cell with =TEXT(--SUBSTITUTE($A1, " at ", " "), "mmmm").

I'm sure you can figure out day, year and string time. For a true time value use either,

=MOD(--SUBSTITUTE($A1, " at ", " "), 1)
=TIMEVALUE(--SUBSTITUTE($A1, " at ", " "))

enter image description here

Upvotes: 0

QHarr
QHarr

Reputation: 84465

If a string as shown:

The basic formula to extract as a date, which I will put in A2 so I can reference A2 going forwards rather than a long formula, assuming A1 has string is

=SUBSTITUTE(TRIM(SUBSTITUTE(Left(A1,find("at",A1)-1),CHAR(32)," ")),",","")

gives:

April 19 2018

I can then extract other parts with

Month

=choose(month(A2),"January","February","March","April","May","June","July","August","September","October","November","December")

Day

=day(A2)

Year

=year(A2)

Time

=right(TRIM(SUBSTITUTE(A1,CHAR(32)," ")),len(TRIM(SUBSTITUTE(A1,CHAR(32)," ")))-(find(":",TRIM(SUBSTITUTE(A1,CHAR(32)," ")),1)-3))

In sheet:

In sheet formulas

You can of course embed the formulas and avoid using A2. I just found it easier to read. An example embedding for day would be:

=day(SUBSTITUTE(TRIM(SUBSTITUTE(Left(A1,find("at",A1)-1),CHAR(32)," ")),",",""))

You could of course also work with DATEVALUE provided you convert start string to something recognised as a date. Example with day again:

  =DAY(DATEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(Left(A1,find("at",A1)-1),CHAR(32)," ")),",","")))

Upvotes: 1

Related Questions