A recent news story reported that 02/02/2020 was the first time in 900 years that we had a palindrome as a date. While the news story left out a bunch of additional details, I was wondering if you could easily fact-check that in Excel.
In today's episode a way to use Dynamic Arrays, SEQUENCE, MID, and CONCAT to reverse the text in a cell.
Also: Check out Fast Excel Speed Tools at FastExcel V4 SpeedTools for a REVERSE.TEXT function.
Check out my Dynamic Arrays book: Guide to Excel Dynamic Arrays by MrExcel
Table of Contents
(0:00) Introduction
(0:20) Clark Kent of Dynamic Array Functions: SEQUENCE
(0:33) Broadcast News Rant
(1:08) Formula to test for a Palindrome
(2:00) Why the Excel formula works, step by step
(2:25) TEXTJOIN versus CONCAT
(2:50) Fast Excel Speed Tools from Charles Williams
(3:05) Generating every date from 1900 to 2140 in one SEQUENCE formula
(4:00) Formatting all of the dates in MMDDYYYY using TEXT function
(4:32) Checking to see if the reverse is equal to the original
(4:40) Joe McDaid fixed the double-click problem with array formulas
(4:55) Using the Filter to find all palindrome dates
(5:05) List of date palindromes in MM/DD/YYYY format
(5:20) Next palindrome date is December 2, 2021
(5:25) Applying red font to all visible cells
(5:41) Using Reapply on a filter in Excel
(5:50) December 12, 2121 will be a palindrome in both formats
(6:20) You don't have to select inside the filtered data set in order to Reapply filter
(6:56) Mention of Dynamic Arrays Straight to the Point book.
In today's episode a way to use Dynamic Arrays, SEQUENCE, MID, and CONCAT to reverse the text in a cell.
Also: Check out Fast Excel Speed Tools at FastExcel V4 SpeedTools for a REVERSE.TEXT function.
Check out my Dynamic Arrays book: Guide to Excel Dynamic Arrays by MrExcel
Table of Contents
(0:00) Introduction
(0:20) Clark Kent of Dynamic Array Functions: SEQUENCE
(0:33) Broadcast News Rant
(1:08) Formula to test for a Palindrome
(2:00) Why the Excel formula works, step by step
(2:25) TEXTJOIN versus CONCAT
(2:50) Fast Excel Speed Tools from Charles Williams
(3:05) Generating every date from 1900 to 2140 in one SEQUENCE formula
(4:00) Formatting all of the dates in MMDDYYYY using TEXT function
(4:32) Checking to see if the reverse is equal to the original
(4:40) Joe McDaid fixed the double-click problem with array formulas
(4:55) Using the Filter to find all palindrome dates
(5:05) List of date palindromes in MM/DD/YYYY format
(5:20) Next palindrome date is December 2, 2021
(5:25) Applying red font to all visible cells
(5:41) Using Reapply on a filter in Excel
(5:50) December 12, 2121 will be a palindrome in both formats
(6:20) You don't have to select inside the filtered data set in order to Reapply filter
(6:56) Mention of Dynamic Arrays Straight to the Point book.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2313. Checking for palindromes, I wait before you say, oh, that sounds so boring. I don't want to see this.
There's a lot of cool Excel in this video, including the first time I've ever used that stupid useless function.
CONCAT and yet another two amazing uses for the Clark Kent of Excel dynamic arrays SEQUENCE. Alright problem today.
Broadcast news oversimplifies things to fit into a 30-second story.
This past weekend, big news item 02/02/2020 is a palindrome. Hasn't happened for 900 years.
Won't happen again for 1000 years. This is my brother-in-law.
At least it's the wardrobe my brother lives is.
"Hang on a second 10/02/2001 was a palindrome.
That was less than 900 years ago. Well the problem is: this wasn't the real story.
The real story is this is that, 02/02/20202 is a palindrome in all *four* date formats.
That is right, that won't happen again in our lifetime.
But based on that first story, we thought, "wait a second. That doesn't seem right.
There has to be other palindromes".
I was thinking is there way in Excel to test for a palindrome?
and I thought that this would be a great use for the new dynamic arrays.
So what I'm going to do is I'm going to =CONCAT, and what I want is the MID of this word over here Apple alright and here which number do I want to start with?
I want to start with a whole bunch of numbers.
I'm gonna use the sequence function. I want one row of numbers.
And how many columns? I want the length of A2 and where do I want to start?
I want to start at the length of A2.
What direction I want to go? I want to go negative one.
Right, so the mid of A2 starting at all of these numbers, how long do I want it? I want it to be 1 number.
And I want to send that whole thing into the concat function so it takes that word Apple an it reverses it.
Why does this work? Let's just look down here at Elderberry for example.
That's a nice long one. What's the length of A7?
The length of A7 is 10.
If I ask for the sequence of one row 10 columns starting at 10.
Minus one, I get the numbers 10 to 1 like that, but then if I use the mid of A7 asking for the sequence it generates those ten letters.
Elderberry but backwards and then CONCAT of above.
I have to tell you when I was preparing for this video I started out using TEXTJOIN but TEXTJOIN makes me say what goes in between each letter and what to do if there's empty cells and in fact I really don't need that.
So I would say the TEXTJOIN is an amazing function. It just runs circles around CONCAT.
But in this particular case CONCAT is shorter and simpler, and that's great.
And hey, by the way a couple of days ago on Monday I showed you that great new fast Excel from Charles Williams.
It has a function that would just make this whole podcast be 10 seconds Reverse.Text.
So if you happen to have bought that, well that's an easier way to go.
Alright, so let's go back to our problem. Are there other dates that are palindromes?
In Excel, the very first date starts January 1st, 1900 so we can't test anything before that.
I'm just going to generate a whole bunch of dates here.
And I'm going to do how many rows.
Let's see if I put in TODAY(), it will give me every day from January 1st, 1900 up until today, but I want to look into the future too.
So I'm going to say today times 2. One column wide.
Starting at one - that's January 1st 1900 - Stepping by one.
Wait - 1,1,1 I don't need that, that's just that's the default.
So very simply here sequence of TODAY()*2 will generate all of the dates, generate all of the dates if I would change this into a short date, you see that it goes all the way out to 2140.
Alright, That's longer than any of us are going to live, but in fact I don't want this to be dates at all because I want to format it into mm/dd/yyyy format so that one formula there I'm going to wrap it in the text function.
And, what text do we want to use?
We want to use this format right here.
Press Enter.
And there we have all of the dates from 01/01/1900 - January 1st 1900 out to March 9th of 2140. That's our test.
Alright, I already wrote this great formula here to put it backwards. Copy that.
And then ask, is this equal to that?
Alright, I'm going to double Click to copy this down.
That bug in Excel has finally been fixed.
It used to be that if that was an array formula over there. double-click was not working.
I reported that to Joe McDaid and he said oh we will get to it.
We'll get to it. Well, great news. They finally got to it.
So that's an exciting thing right there and then turn on the filter and look for things that are true and will find all of the dates in mmddyy format that are palindromes.
So here's the one that just happened. 02/02/2020.
But it also happened on November 2nd, 2011.
It's going to happen again on December 2nd, 2021, March 2nd 2030.
So hey, if you miss this past Sunday look, it's going to happen again.
Now here I'm going to make all of these be red.
And then up here we'll change to the European date format DDMMYYYY.
All right, and then I want to look again for the things that are true. do I have to open that drop-down now?
I can go to the Data tab and choose Reapply.
That'll get me all the TRUEs, and sure enough, there was only one that is a palindrome in both. Although... 12/12/2021.
Sorry, I'm not going to make it.
There may be my great grandkids will make it there and then we can also look for palindromes in YYYYMMDD.
And Reapply.
Alright whole bunch there and then in YYYY DD MM.
And Reapply.
And a whole bunch there, right?
So hey, cool way to check for palindromes using the CONCAT of the MID of the SEQUENCE using LENgth cool little formula there also a great way to generate.
Well, heck every date in our lifetime using one formula up in A2.
And Kudos to my brother-in-law.
You're right, there's lots of other days that are palindromes, IF this was the news story that you heard. Now dynamic arrays - brand new in Excel.
I check out this ebook - $3 - Excel Dynamic Array Straight to the Point. Click that "i" in the top right-hand corner.
Have you enjoyed useless facts like this while learning some cool things about Excel? Please Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
There's a lot of cool Excel in this video, including the first time I've ever used that stupid useless function.
CONCAT and yet another two amazing uses for the Clark Kent of Excel dynamic arrays SEQUENCE. Alright problem today.
Broadcast news oversimplifies things to fit into a 30-second story.
This past weekend, big news item 02/02/2020 is a palindrome. Hasn't happened for 900 years.
Won't happen again for 1000 years. This is my brother-in-law.
At least it's the wardrobe my brother lives is.
"Hang on a second 10/02/2001 was a palindrome.
That was less than 900 years ago. Well the problem is: this wasn't the real story.
The real story is this is that, 02/02/20202 is a palindrome in all *four* date formats.
That is right, that won't happen again in our lifetime.
But based on that first story, we thought, "wait a second. That doesn't seem right.
There has to be other palindromes".
I was thinking is there way in Excel to test for a palindrome?
and I thought that this would be a great use for the new dynamic arrays.
So what I'm going to do is I'm going to =CONCAT, and what I want is the MID of this word over here Apple alright and here which number do I want to start with?
I want to start with a whole bunch of numbers.
I'm gonna use the sequence function. I want one row of numbers.
And how many columns? I want the length of A2 and where do I want to start?
I want to start at the length of A2.
What direction I want to go? I want to go negative one.
Right, so the mid of A2 starting at all of these numbers, how long do I want it? I want it to be 1 number.
And I want to send that whole thing into the concat function so it takes that word Apple an it reverses it.
Why does this work? Let's just look down here at Elderberry for example.
That's a nice long one. What's the length of A7?
The length of A7 is 10.
If I ask for the sequence of one row 10 columns starting at 10.
Minus one, I get the numbers 10 to 1 like that, but then if I use the mid of A7 asking for the sequence it generates those ten letters.
Elderberry but backwards and then CONCAT of above.
I have to tell you when I was preparing for this video I started out using TEXTJOIN but TEXTJOIN makes me say what goes in between each letter and what to do if there's empty cells and in fact I really don't need that.
So I would say the TEXTJOIN is an amazing function. It just runs circles around CONCAT.
But in this particular case CONCAT is shorter and simpler, and that's great.
And hey, by the way a couple of days ago on Monday I showed you that great new fast Excel from Charles Williams.
It has a function that would just make this whole podcast be 10 seconds Reverse.Text.
So if you happen to have bought that, well that's an easier way to go.
Alright, so let's go back to our problem. Are there other dates that are palindromes?
In Excel, the very first date starts January 1st, 1900 so we can't test anything before that.
I'm just going to generate a whole bunch of dates here.
And I'm going to do how many rows.
Let's see if I put in TODAY(), it will give me every day from January 1st, 1900 up until today, but I want to look into the future too.
So I'm going to say today times 2. One column wide.
Starting at one - that's January 1st 1900 - Stepping by one.
Wait - 1,1,1 I don't need that, that's just that's the default.
So very simply here sequence of TODAY()*2 will generate all of the dates, generate all of the dates if I would change this into a short date, you see that it goes all the way out to 2140.
Alright, That's longer than any of us are going to live, but in fact I don't want this to be dates at all because I want to format it into mm/dd/yyyy format so that one formula there I'm going to wrap it in the text function.
And, what text do we want to use?
We want to use this format right here.
Press Enter.
And there we have all of the dates from 01/01/1900 - January 1st 1900 out to March 9th of 2140. That's our test.
Alright, I already wrote this great formula here to put it backwards. Copy that.
And then ask, is this equal to that?
Alright, I'm going to double Click to copy this down.
That bug in Excel has finally been fixed.
It used to be that if that was an array formula over there. double-click was not working.
I reported that to Joe McDaid and he said oh we will get to it.
We'll get to it. Well, great news. They finally got to it.
So that's an exciting thing right there and then turn on the filter and look for things that are true and will find all of the dates in mmddyy format that are palindromes.
So here's the one that just happened. 02/02/2020.
But it also happened on November 2nd, 2011.
It's going to happen again on December 2nd, 2021, March 2nd 2030.
So hey, if you miss this past Sunday look, it's going to happen again.
Now here I'm going to make all of these be red.
And then up here we'll change to the European date format DDMMYYYY.
All right, and then I want to look again for the things that are true. do I have to open that drop-down now?
I can go to the Data tab and choose Reapply.
That'll get me all the TRUEs, and sure enough, there was only one that is a palindrome in both. Although... 12/12/2021.
Sorry, I'm not going to make it.
There may be my great grandkids will make it there and then we can also look for palindromes in YYYYMMDD.
And Reapply.
Alright whole bunch there and then in YYYY DD MM.
And Reapply.
And a whole bunch there, right?
So hey, cool way to check for palindromes using the CONCAT of the MID of the SEQUENCE using LENgth cool little formula there also a great way to generate.
Well, heck every date in our lifetime using one formula up in A2.
And Kudos to my brother-in-law.
You're right, there's lots of other days that are palindromes, IF this was the news story that you heard. Now dynamic arrays - brand new in Excel.
I check out this ebook - $3 - Excel Dynamic Array Straight to the Point. Click that "i" in the top right-hand corner.
Have you enjoyed useless facts like this while learning some cool things about Excel? Please Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
