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?
Please use the following
(adjust the range to your needs and format as date)
How the formula works:
REGEXREPLACE takes only text values we use
TEXT to turn the numbers to text, edit them and then turn them again to numbers using
We also use the
IF function to get rid of blank cells and
ArrayFormula to do the whole column in one go.