How can you reverse text in Excel? There is not a REVERSE function. You've recently seen me do this with a VBA loop. Lianna Gerrish from the Financial Modeling World Cup Semi-Finals would flip it and reverse it with strREVERSE in VBA.
Dan Mayoh and Jose Carlos Canejo both sent in a way to do it with native Excel functions. But... how do you ever remember CONCAT, LEFT, RIGHT, SEQUENCE, LEN?
In a special guest appearance on the 40th anniversary of the release of 867-5309 (Jenny), the lead singer of Tommy Tutone joins us with a song from his upcoming album - Music for Fellow Nerds. Listen to "Search in Reverse" a few hundred times and you will have it down!
Also in this episode:
Bill's secret skill to say the alphabet backwards.
One of the two post-it notes on Bill's cube wall from 1991-1999 reveals why he still uses VLOOKUP instead of Index and Match.
Many thanks to Tommy Tutone for having a great sense of humor and going along with my insane request.
Links
To enter the Financial Modelling World Cup: FMWC Open – FMWC
Tommy Tutone: Tommy Tutone
Tommy Tutone on Cameo: Cameo - Tommy Tutone
Chicago White Sox Organist Nancy Faust on Cameo: Cameo - Nancy Faust
Dan Mayoh: Fintega | Australia | Financial Models
867-5309/Jenny on YouTube:
Table of Contents
(0:00) Finding text that is backwards
(0:40) Reverse Excel text using strReverse from Lianna Gerish
(1:06) Dan Mayoh schooling me on native functions
(1:38) Index/Match post-it note
(1:56) Saying the alphabet backwards
(2:15) 40-years ago, 867-5309 (Jenny) was released
(2:52) Tommy Tutone sings Search in Reverse
(3:57) How the search in reverse function works
(4:48) Wishing for =REVERSE
(4:59) Hire Tommy Tutone on Cameo
Dan Mayoh and Jose Carlos Canejo both sent in a way to do it with native Excel functions. But... how do you ever remember CONCAT, LEFT, RIGHT, SEQUENCE, LEN?
In a special guest appearance on the 40th anniversary of the release of 867-5309 (Jenny), the lead singer of Tommy Tutone joins us with a song from his upcoming album - Music for Fellow Nerds. Listen to "Search in Reverse" a few hundred times and you will have it down!
Also in this episode:
Bill's secret skill to say the alphabet backwards.
One of the two post-it notes on Bill's cube wall from 1991-1999 reveals why he still uses VLOOKUP instead of Index and Match.
Many thanks to Tommy Tutone for having a great sense of humor and going along with my insane request.
Links
To enter the Financial Modelling World Cup: FMWC Open – FMWC
Tommy Tutone: Tommy Tutone
Tommy Tutone on Cameo: Cameo - Tommy Tutone
Chicago White Sox Organist Nancy Faust on Cameo: Cameo - Nancy Faust
Dan Mayoh: Fintega | Australia | Financial Models
867-5309/Jenny on YouTube:
Table of Contents
(0:00) Finding text that is backwards
(0:40) Reverse Excel text using strReverse from Lianna Gerish
(1:06) Dan Mayoh schooling me on native functions
(1:38) Index/Match post-it note
(1:56) Saying the alphabet backwards
(2:15) 40-years ago, 867-5309 (Jenny) was released
(2:52) Tommy Tutone sings Search in Reverse
(3:57) How the search in reverse function works
(4:48) Wishing for =REVERSE
(4:59) Hire Tommy Tutone on Cameo
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2444. An easier way to reverse text.
Hey, welcome back to the MrExcel netcast. I am Bill jeLEN.
Today a question from me.
Actually, you know when we're trying to FIND a word inside some other text, we have two options here.
We have FIND and we have SEARCH that work really great, so they have no problem FINDing Jenny.
Unless Jenny is backwards RIGHT then FIND and SEARCH don't have any way to say go backwards.
So the question is how do we reverse text so we can use FIND or SEARCH?
You've seen me do it just recently - three weeks ago in video 2439, I switched over into VBA and wrote this little loop.
If you watched the Financial Modeling World Cup semi-finals Lianna, who was competing head-to-head with like 5 minutes left to go.
Flips over to VBA, and has the forethought to name her function, FlipItAndReverseIt. With this StrReverse, which is so much simpler.
But during this broadcast, Dan Mayoh, who was my Co-host on the show, said.
“Well, you know you could do this with native functions…”.
And you can see me just shaking my head like no, I'm not going to be able to do with native functions.
Because the native functions are too **** confusing.
A comment on that video from Jose Carlos said “Oh no, hey yeah you can do it”. It's just CONCAT LEFT RIGHT SEQUENCE LENgth.
I'm like what? how am I ever going to remember CONCAT LEFT, RIGHT SEQUENCE LENgth?
I'm not making this up: from 1991 to 1999, I was working in Akron, ohio.
And I had two Post-it notes on my wall.
One of them was this just simply trying to nest two functions.
How the MATCH goes inside the INDEX and now it's five functions? Look, it's not that I can't learn.
You know, over the years I've taken various memory training courses.
And and one I remember taught me how to say the alphabet backwards.
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.
And it was just 40 years ago this week that Tommy Tutone taught the entire world how to memorize this number. 1981 November 1981 Jenny 867-5309.
We all still remember that and so I'm I'm wondering how am I ever going to remember CONCAT LEFT RIGHT SEQUENCE LENgth. It turns out, that Tommy Tutone is still alive.
He had a long career in I.T..
He still plays and still plays Jenny and I reached out. And here's his suggestion.
[ Tom ]OK, I'm going to teach you how to search in reverse.
Repeat after me. It's easy with five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN. Your turn!
CONCAT, LEFT, RIGHT SEQUENCE LEN. That's how you search in reverse.
That's how you turn it around. Its just five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN. CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN. CONCAT, LEFT, RIGHT SEQUENCE LEN.
I think you got it.
That's on my next record: Music For Fellow Nerds. Take care of yourself.
[ Bill ] CONCAT LEFT, RIGHT SEQUENCE LENgth. Well, I think I've got it.
I've got it now. Now the question is how does this work?
How do we take this string and turn it into “Jenny I've got your number”?
Inside, starting on the inside the LENgth of A3 is 26.
The SEQUENCE of 26 gives us numbers one through 26 as a spillable array, which is awesome.
And then the RIGHT of A3 starting with 1, then 2, then 3, then 4.
Works backwards from the end of the string until we get the whole string backwards at the very last one.
And then the LEFT, the LEFT gets the first letter: “Jenny I've got your number”. And then CONCAT puts it all together.
So there is a great way to do it.
Five functions if you can just remember this hot little tune from Tommy Tutone. Check out my book MrExcel 2021 Unmasking Excel.
Lots of great formulas in there, although certainly not the reverse formula.
Wouldn't it be simpler if Microsoft just gave us =REVERSE?
Of course then we never would have heard this great song Hey yeah, shout out to Tommy Tutone. You can find him on Cameo.
He will sing a nice little song for your next birthday, anniversary or whatever.
Check it out - great guy.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to ask any questions or comments down in the YouTube comments below. Well, I want to thank you for stopping by.
Thanks to Tommy for agreeing to let me use this song here in this video.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill jeLEN.
Today a question from me.
Actually, you know when we're trying to FIND a word inside some other text, we have two options here.
We have FIND and we have SEARCH that work really great, so they have no problem FINDing Jenny.
Unless Jenny is backwards RIGHT then FIND and SEARCH don't have any way to say go backwards.
So the question is how do we reverse text so we can use FIND or SEARCH?
You've seen me do it just recently - three weeks ago in video 2439, I switched over into VBA and wrote this little loop.
If you watched the Financial Modeling World Cup semi-finals Lianna, who was competing head-to-head with like 5 minutes left to go.
Flips over to VBA, and has the forethought to name her function, FlipItAndReverseIt. With this StrReverse, which is so much simpler.
But during this broadcast, Dan Mayoh, who was my Co-host on the show, said.
“Well, you know you could do this with native functions…”.
And you can see me just shaking my head like no, I'm not going to be able to do with native functions.
Because the native functions are too **** confusing.
A comment on that video from Jose Carlos said “Oh no, hey yeah you can do it”. It's just CONCAT LEFT RIGHT SEQUENCE LENgth.
I'm like what? how am I ever going to remember CONCAT LEFT, RIGHT SEQUENCE LENgth?
I'm not making this up: from 1991 to 1999, I was working in Akron, ohio.
And I had two Post-it notes on my wall.
One of them was this just simply trying to nest two functions.
How the MATCH goes inside the INDEX and now it's five functions? Look, it's not that I can't learn.
You know, over the years I've taken various memory training courses.
And and one I remember taught me how to say the alphabet backwards.
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.
And it was just 40 years ago this week that Tommy Tutone taught the entire world how to memorize this number. 1981 November 1981 Jenny 867-5309.
We all still remember that and so I'm I'm wondering how am I ever going to remember CONCAT LEFT RIGHT SEQUENCE LENgth. It turns out, that Tommy Tutone is still alive.
He had a long career in I.T..
He still plays and still plays Jenny and I reached out. And here's his suggestion.
[ Tom ]OK, I'm going to teach you how to search in reverse.
Repeat after me. It's easy with five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN. Your turn!
CONCAT, LEFT, RIGHT SEQUENCE LEN. That's how you search in reverse.
That's how you turn it around. Its just five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN. CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN. CONCAT, LEFT, RIGHT SEQUENCE LEN.
I think you got it.
That's on my next record: Music For Fellow Nerds. Take care of yourself.
[ Bill ] CONCAT LEFT, RIGHT SEQUENCE LENgth. Well, I think I've got it.
I've got it now. Now the question is how does this work?
How do we take this string and turn it into “Jenny I've got your number”?
Inside, starting on the inside the LENgth of A3 is 26.
The SEQUENCE of 26 gives us numbers one through 26 as a spillable array, which is awesome.
And then the RIGHT of A3 starting with 1, then 2, then 3, then 4.
Works backwards from the end of the string until we get the whole string backwards at the very last one.
And then the LEFT, the LEFT gets the first letter: “Jenny I've got your number”. And then CONCAT puts it all together.
So there is a great way to do it.
Five functions if you can just remember this hot little tune from Tommy Tutone. Check out my book MrExcel 2021 Unmasking Excel.
Lots of great formulas in there, although certainly not the reverse formula.
Wouldn't it be simpler if Microsoft just gave us =REVERSE?
Of course then we never would have heard this great song Hey yeah, shout out to Tommy Tutone. You can find him on Cameo.
He will sing a nice little song for your next birthday, anniversary or whatever.
Check it out - great guy.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to ask any questions or comments down in the YouTube comments below. Well, I want to thank you for stopping by.
Thanks to Tommy for agreeing to let me use this song here in this video.
We'll see you next time for another netcast from MrExcel.
