Microsoft Excel Tutorial:
Three ways to write a function to reverse the characters in a cell in Excel.
Fast Excel from Charles Williams offers a REVERSE function: FastExcel V4 SpeedTools
Table of Contents
(0:00) Welcome and set up
(1:09) Using a VBA macro to reverse the characters
(2:20) Using a friendly LAMBDA that calls a recursive LAMBDA.
(6:38) Using the SEQUENCE function and CONCAT
Three ways to write a function to reverse the characters in a cell in Excel.
Fast Excel from Charles Williams offers a REVERSE function: FastExcel V4 SpeedTools
Table of Contents
(0:00) Welcome and set up
(1:09) Using a VBA macro to reverse the characters
(2:20) Using a friendly LAMBDA that calls a recursive LAMBDA.
(6:38) Using the SEQUENCE function and CONCAT
Transcript of the video:
Learn Excel from MrExcel podcast episode 2391. Reverse the characters in the cell, three different ways.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen. After yesterday's episode 2390 with the recursive Lambda, I wanted to see if I could actually do it myself.
So today we have three different ways.
The first one is a quick little three line macro.
The next one is a recursive reversive Lambda.
[ laughs at his own joke ] Get that?
An idea from Geert, an amended by Rico.
By the way, there is a great conversation in the comments for 2390 between those three.
As far as whether or not I should do what I'm doing today.
But I am going to do it anyway.
And then the final way is the way that I woke up at 3:57 AM this morning with a completely different way using SEQUENCE.
Although then as I was working on Bob Umlas’ new book “Cool Excel Sh*t”, I realized that the formula is in the book and I probably didn't dream it up myself.
I probably heard it from Bob.
Alright, but first before we talk about doing things in reverse.
I have this amazing secret talent.
Let me show you.
Z-Y-X-W-V-U-T-S-R-Q-P-O-N-M-L-K-J-I-H-G-F-E-D-C-B-A!
[ laughs ] I don’t know what that is good for.
OKay, so here's what we want to do.
We want to take these letters in column A and completely reverse them.
By the way, Charles Williams, his Fast Excel add-in has a function that does this very thing.
But I am going to do it with VBA.
I've done it this way 1000 times, I am sure.
Alt+F11.
We are going to have a function called Invert.
We pass it a phrase.
We say For I equals the length of the phrase to one, step minus one.
And we just say invert, that's a variable.
The function name is the variable we want to build.
..is equal to Invert ampersand the MID of phrase, comma I comma one.
Next I.
When it's done it just passes it back.
So simple and easy.
I can do it without thinking about it.
So =INVERT( that phrase).
Double-click and copy it down.
And life is great.
Of course, it's never going to work in Excel Online, but that won't bother me because I'm never going to be in Excel Online.
But someone might.
Alright, so now let's do it with Lambda.
I'm going to create a Lambda called Rev.
And unlike yesterday's Lambda, I'm not going to pass at the index.
Rev is the user-friendly version of this function.
It requires just one argument - the phrase.
And then Rev is secretly going to call ZRev.
Why is it called ZRev?
Originally I called it RevInternal, But then when they're typing the formulam they will have a choice between Rev and RevInternal and that will cause confusion.
Alright, so Rev secretly calls ZRev with the original argument - the phrase, an empty string and the length of the phrase.
Now ZRev is going to take those three arguments, phrase, the reverse phrase, and number I the length.
And it's going to say “alright, are we're done?
Have we gotten all the way back to character zero?” If we have, then return the Rev phrase.
If we have not, then the RevPhrase is the RevPhrase and the character I from phrase.
And then subtract 1 - or decrement - I.
And then calls the zRev again with Phrase, RevPphrase, and I.
Now you know the big problem here is we're building this in the Name Manager.
Which means that I am building it in a text box.
And in the text box they're not color coding the parentheses for me.
So I just had to figure out here: I'm going to close the LET.
Close the IF. Close the Lambda.
So four parentheses at the end.
Alright, a couple of things here.
Just remember the syntax for Lambda In the first line we have as many arguments as you want to pass its.
Argument 1, argument 2, argument 3, argument N.
And then after that, after that last comma, the Formula.
ONE formula that has to calculate the answer.
The new LET function.
It has pairs at first.
So variable 1, and the definition for variable 1.
Variable two and the definition for that.
Variable 3 and the definition for that.
And then finally after the last comma, the formula to calculate the answer.
Alright, so here we go.
My definition for Rev. I'm going to pass it one argument, a phrase.
All it is going to do is call zRev passing that phrase, an empty string, and the length of the phrase.
That's kind of like in my VBA function I said For I = LEN(Phrase) to 1 Step -1.
And then here's our definition for zRev.
It's getting three arguments: the phrase, the Rev phrase and I.
If I has already made it back to zero, then we are done - just pass the RevPhrase back up to Rev. Otherwise - a LET function here.
We're going to let the RevPhrase, as a variable name,. be equal to the RevPhrase so far.
And the MID of Phrase, the “I” character, comma one.
And then we decrement “I” with I is equal to I minus one.
And then, here we go, Lambda calling itself, zRev of Phrase comma RevPhrase, I with those 1,2,3,4 parentheses at the end to close the zRev, the LET, the IF, and the LAMBDA.
Alright, so now yesterday I skipped over this.
Let's just build it here.
So we have something that we're going to call Rev.
I copy that from the text box.
I go into Formulas.
Go into the Name Manager.
Define a new name.
That name is going to be called Rev.
And it's going to refer to this.
Let's see if that works.
Beautiful.
And then close this.
Come back here and grab the text from zRev.
Control C. Name manager.
New Name.
This is going to be called zRev.
And that is going to be that phrase.
It looks great.
Click close.
Alright, this will be the first time I actually wrote one of these on my own.
So =REV, very easy, and pass in the phrase and life is great.
Double click and copy it down and it works.
OK now, Rico S, he pointed out.
He told me this was going to happen.
So I type =Rev and they're offering me Rev or ZRev.
Rico's idea was some big long thing.
Like Hey - please don't use this - zRev.
Alright but I didn't listen.
Alright, so that's the 2nd way.
Then the 3rd way.
Literally at 3:57 AM this morning I was lying there in bed thinking about this podcast.
And I said wait a second, there's a completely easier way to do this.
Alright, so we have our phrase.
I want to know the LENgth of the phrase.
So there's 44 characters in that phrase.
So what I'm going to do here is I'm going to the MID of A1.
So where am I going to start?
I'm going to include all of the numbers using the SEQUENCE function.
The SEQUENCE function is going to be how many rows?
44 in this case.
The LEN of A1.
That many rows.
One column.
Where do I start?
I started the LENgth of A1.
And what's the step?
Negative one.
Alright, so that's going to return a sequence of 44, 43, 42, all the way down to one.
And I want the MID for 1 character long.
And when I press Enter there, that phrase is going to get strung down the column, backwards.
Ad then just simply wrap that in the awesome from February 2017 - six functions came out this day - and I said I'm never going to use CONCAT because TEXTJOIN is superior.
But in this case CONCAT is the answer.
We don't need column B, that was just a temporary thing.
Double click and copy that down The day that SEQUENCE came out, I said compared to SORT, FILTER, SORTBY, and UNIQUE, that SEQUENCE is the weak one here.
Nope, SEQUENCE is the Randy of dynamic arrays.
It looks like just a mild mannered accountant, but there are super powers.
Sequence might be just the fastest way to do this.
It works in Excel online, You don't have to think about the Lambdas.
Probably a great way to go.
All of thatL Lambdas, LET, SEQUENCE in my new book MrExcel 2021 Unmasking Excel.
Click that I in the top right hand corner for more information.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to think stopping by.
I’ll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen. After yesterday's episode 2390 with the recursive Lambda, I wanted to see if I could actually do it myself.
So today we have three different ways.
The first one is a quick little three line macro.
The next one is a recursive reversive Lambda.
[ laughs at his own joke ] Get that?
An idea from Geert, an amended by Rico.
By the way, there is a great conversation in the comments for 2390 between those three.
As far as whether or not I should do what I'm doing today.
But I am going to do it anyway.
And then the final way is the way that I woke up at 3:57 AM this morning with a completely different way using SEQUENCE.
Although then as I was working on Bob Umlas’ new book “Cool Excel Sh*t”, I realized that the formula is in the book and I probably didn't dream it up myself.
I probably heard it from Bob.
Alright, but first before we talk about doing things in reverse.
I have this amazing secret talent.
Let me show you.
Z-Y-X-W-V-U-T-S-R-Q-P-O-N-M-L-K-J-I-H-G-F-E-D-C-B-A!
[ laughs ] I don’t know what that is good for.
OKay, so here's what we want to do.
We want to take these letters in column A and completely reverse them.
By the way, Charles Williams, his Fast Excel add-in has a function that does this very thing.
But I am going to do it with VBA.
I've done it this way 1000 times, I am sure.
Alt+F11.
We are going to have a function called Invert.
We pass it a phrase.
We say For I equals the length of the phrase to one, step minus one.
And we just say invert, that's a variable.
The function name is the variable we want to build.
..is equal to Invert ampersand the MID of phrase, comma I comma one.
Next I.
When it's done it just passes it back.
So simple and easy.
I can do it without thinking about it.
So =INVERT( that phrase).
Double-click and copy it down.
And life is great.
Of course, it's never going to work in Excel Online, but that won't bother me because I'm never going to be in Excel Online.
But someone might.
Alright, so now let's do it with Lambda.
I'm going to create a Lambda called Rev.
And unlike yesterday's Lambda, I'm not going to pass at the index.
Rev is the user-friendly version of this function.
It requires just one argument - the phrase.
And then Rev is secretly going to call ZRev.
Why is it called ZRev?
Originally I called it RevInternal, But then when they're typing the formulam they will have a choice between Rev and RevInternal and that will cause confusion.
Alright, so Rev secretly calls ZRev with the original argument - the phrase, an empty string and the length of the phrase.
Now ZRev is going to take those three arguments, phrase, the reverse phrase, and number I the length.
And it's going to say “alright, are we're done?
Have we gotten all the way back to character zero?” If we have, then return the Rev phrase.
If we have not, then the RevPhrase is the RevPhrase and the character I from phrase.
And then subtract 1 - or decrement - I.
And then calls the zRev again with Phrase, RevPphrase, and I.
Now you know the big problem here is we're building this in the Name Manager.
Which means that I am building it in a text box.
And in the text box they're not color coding the parentheses for me.
So I just had to figure out here: I'm going to close the LET.
Close the IF. Close the Lambda.
So four parentheses at the end.
Alright, a couple of things here.
Just remember the syntax for Lambda In the first line we have as many arguments as you want to pass its.
Argument 1, argument 2, argument 3, argument N.
And then after that, after that last comma, the Formula.
ONE formula that has to calculate the answer.
The new LET function.
It has pairs at first.
So variable 1, and the definition for variable 1.
Variable two and the definition for that.
Variable 3 and the definition for that.
And then finally after the last comma, the formula to calculate the answer.
Alright, so here we go.
My definition for Rev. I'm going to pass it one argument, a phrase.
All it is going to do is call zRev passing that phrase, an empty string, and the length of the phrase.
That's kind of like in my VBA function I said For I = LEN(Phrase) to 1 Step -1.
And then here's our definition for zRev.
It's getting three arguments: the phrase, the Rev phrase and I.
If I has already made it back to zero, then we are done - just pass the RevPhrase back up to Rev. Otherwise - a LET function here.
We're going to let the RevPhrase, as a variable name,. be equal to the RevPhrase so far.
And the MID of Phrase, the “I” character, comma one.
And then we decrement “I” with I is equal to I minus one.
And then, here we go, Lambda calling itself, zRev of Phrase comma RevPhrase, I with those 1,2,3,4 parentheses at the end to close the zRev, the LET, the IF, and the LAMBDA.
Alright, so now yesterday I skipped over this.
Let's just build it here.
So we have something that we're going to call Rev.
I copy that from the text box.
I go into Formulas.
Go into the Name Manager.
Define a new name.
That name is going to be called Rev.
And it's going to refer to this.
Let's see if that works.
Beautiful.
And then close this.
Come back here and grab the text from zRev.
Control C. Name manager.
New Name.
This is going to be called zRev.
And that is going to be that phrase.
It looks great.
Click close.
Alright, this will be the first time I actually wrote one of these on my own.
So =REV, very easy, and pass in the phrase and life is great.
Double click and copy it down and it works.
OK now, Rico S, he pointed out.
He told me this was going to happen.
So I type =Rev and they're offering me Rev or ZRev.
Rico's idea was some big long thing.
Like Hey - please don't use this - zRev.
Alright but I didn't listen.
Alright, so that's the 2nd way.
Then the 3rd way.
Literally at 3:57 AM this morning I was lying there in bed thinking about this podcast.
And I said wait a second, there's a completely easier way to do this.
Alright, so we have our phrase.
I want to know the LENgth of the phrase.
So there's 44 characters in that phrase.
So what I'm going to do here is I'm going to the MID of A1.
So where am I going to start?
I'm going to include all of the numbers using the SEQUENCE function.
The SEQUENCE function is going to be how many rows?
44 in this case.
The LEN of A1.
That many rows.
One column.
Where do I start?
I started the LENgth of A1.
And what's the step?
Negative one.
Alright, so that's going to return a sequence of 44, 43, 42, all the way down to one.
And I want the MID for 1 character long.
And when I press Enter there, that phrase is going to get strung down the column, backwards.
Ad then just simply wrap that in the awesome from February 2017 - six functions came out this day - and I said I'm never going to use CONCAT because TEXTJOIN is superior.
But in this case CONCAT is the answer.
We don't need column B, that was just a temporary thing.
Double click and copy that down The day that SEQUENCE came out, I said compared to SORT, FILTER, SORTBY, and UNIQUE, that SEQUENCE is the weak one here.
Nope, SEQUENCE is the Randy of dynamic arrays.
It looks like just a mild mannered accountant, but there are super powers.
Sequence might be just the fastest way to do this.
It works in Excel online, You don't have to think about the Lambdas.
Probably a great way to go.
All of thatL Lambdas, LET, SEQUENCE in my new book MrExcel 2021 Unmasking Excel.
Click that I in the top right hand corner for more information.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to think stopping by.
I’ll see you next time for another netcast from MrExcel.
