Search code examples
arraysdategoogle-sheetsarray-formulasgoogle-sheets-query

Replacing cell specific seperator in Google Sheets - 21.06.2019 to 21/06/2019

can someone help me in replacing periods with forwarding slashes in sheets? I have a data set with over 100 thousand rows in sheets, I have a date-time formatted as such D.M.Y I need to change it to D/M/Y each row has 7 or so columns based on date-time. anyways, below is the first 6 rows of column 'A' so its format is D.M.Y so I have 100,000 rows of this in column 'A'

Date 21.06.2019 22.06.2019 23.06.2019 24.06.2019 25.06.2019 26.06.2019

I want to turn it into this

Date 21/06/2019 22/06/2019 23/06/2019 24/06/2019 25/06/2019 26/06/2019

I need it in this format as sheet does not recognize the '.' as a separator and it would take an eternity to do it manually, thanks! A link to my screen below to see the format

enter image description here

Solution

  • check if your dates are valid dates with:

    =ISDATE(A2)

    if yes use this in B2:

    =ARRAYFORMULA(TEXT(A2:A; "dd/mm/yyyy"))

    if no you can use:

    =ARRAYFORMULA(SUBSTITUTE(A2:A; "."; "/"))

    0

    and if you need real dates do:

    =ARRAYFORMULA(QUERY(IFERROR(DATEVALUE(SUBSTITUTE(A2:A; "."; "/"))); 
 "format(Col1) 'dd/mm/yyyy'"))

    0