2

I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number 20201130 to 2020/11/30. How could I do it?

example

Improve this question
1
  • Please remember that you can also contribute to this community that helps you. As per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well.
    – marikamitsos
    Commented Jan 10, 2021 at 5:57

1 Answer 1

Reset to default
0

Please use the following

=ArrayFormula(IF(A2:A="",, 
        REGEXREPLACE(TEXT(A2:A,"#"),"(\d{4})(\d{2})(\d{2})","$1/$2/$3")^1))

(adjust the range to your needs and format as date)

enter image description here

How the formula works:
Because REGEXREPLACE takes only text values we use TEXT to turn the numbers to text, edit them and then turn them again to numbers using ^1.
We also use the IF function to get rid of blank cells and ArrayFormula to do the whole column in one go.

Functions used:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.