Is there a way to find and remove the datetime from multiple rows in Google Sheets?

user_11614634 0
Viewed 69

Hope you're doing well.

Imagine I have the following Sheet:

5:20:58 xxxx: entro con el mismo xxxx                           
5:21:08 xxxx: xxxx                          
5:21:58 xxxxx: Perfecto, te pido de 5 a 10 minutos mientras 
reviso la configuración de las etiquetas. ¿De acuerdo?                          
5:22:04 xxxxx: ok

I need to delete the datetime of all those rows. The result

xxxx: entro con el mismo xxxx                           
xxxx: xxxx                          
xxxxx: Perfecto, te pido de 5 a 10 minutos mientras 
reviso la configuración de las etiquetas. ¿De acuerdo?                          
xxxxx: ok

Is there a formula in Google Sheets to make this?
I tried with REPLACE, SPLIT but is not applicable to all the rows in the sheet.

(The real sheet has too many rows, I extracted a part from the sheet to give an example)

google-sheets-formula google-sheets regex
2 Answers

Let's say your raw data is in A2:A. Place this in the second cell (e.g., B2) of an otherwise empty column:

=ArrayFormula(IF(A2:A="",,TRIM(REGEXREPLACE(A2:A,"\d+:\d+:\d+",""))))

ADDENDUM:

Version for some international locales (where semicolon is used in place of a comma within formulas):

=ArrayFormula(IF(A2:A="";;TRIM(REGEXREPLACE(A2:A;"\d+:\d+:\d+";""))))

user_139032830

EDIT

(following OP's comment)

...there are sometimes that the data not starts with a timestamp. ... How can I adjust the formula to make it work?

Please use the following altered formula

=INDEX(IFERROR(REGEXEXTRACT(B1:B;" (.*)");B1:B))

OR (for an even more robust formula)

=INDEX(IFERROR(REGEXEXTRACT(B1:B;"^\d+:\d+:\d+ (.+)");B1:B))

Original answer

Please use the following formula (adjust range to your needs)

=INDEX(IFERROR(REGEXEXTRACT(B1:B;" (.*)")))

OR (depending on your locale)

=INDEX(IFERROR(REGEXEXTRACT(B1:B," (.*)")))

enter image description here

Functions used:

user_15277800
Related