1

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

  • 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 Jan 10 at 5:57
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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