Building a SLUGIFY function in Excel using the brand new recursive LAMBDA function. This video shows how to use an Index argument to act as a loop counter in an Excel formula. Thanks to Smozgur who posted this example at the MrExcel Message Board LAMBDA forum: Excel LAMBDA Functions
The recursive nature of LAMBDA functions is somewhat hard to wrap your head around. This example shows how one LAMBDA formula replaces 220+ rows of regular Excel formulas.
The recursive nature of LAMBDA functions is somewhat hard to wrap your head around. This example shows how one LAMBDA formula replaces 220+ rows of regular Excel formulas.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2390.
Solving SLUGIFY with with recursive lambdas.
Yes, I am going to try and explain these recursive lambdas in plain English.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today an example from my friend Smozgur at the MrExcel Message Board.
There's a problem in web development.
Where you have to take an article title and convert it to HTML.
And that process is called Slugify. So they take an article title, convert to lowercase, keep anything that's a through Z or zero through 9.
And everything else becomes a dash.
But then at the end if you have consecutive dashes, you have to change it to a single dash.
And today I'm going to show you how we can solve this using something called a recursive Lambda.
That's where one of these new Lambda functions in Excel calls itself, which is crazy.
It's essentially doing a loop, like you would do in VBA.
And I'm going to show you step-by-step, in plain English how that works.
Before I do that I just want to note that this could have been done in Excel without Lambdas.
But it was super ugly.
Here's one where I take a phrase and character by character, splitting it out using the MID function.
Check to see what the character code is.
Is that character code between 97 and 122 or 48 and 57?
That's a through z and zero through 9.
Get everything before that character.
Get that character or a dash.
Get everything to the right of that character.
Put it back together.
And then increment the index by one.
Are we done?
Are we more than the length of G3?
And if we're not, then go back around.
Down here, sixty-four rows of formulas.
I finally get the end result.
But it's ridiculous to do it this way.
The other way is to say alright, there are 36 characters we want to keep.
Let's do SUBSTITUTE 219 times.
Here's all the different SUBSTITUTEs.
Getting rid of all the bad characters.
And then finally at the end, one more SUBSTITUTE where we do a TRIM of the SUBSTITUTE of every dash to a space.
That gets rid of the multiple dashes.
And then change it back to a dash.
Still not a fun way to go, right?
And we can solve this whole thing.
Any title over here in Column A.
Using the =slugify function that Smozgur wrote and posted in the great section of the MrExcel Message Board where people are posting their cool Lambdas.
I'll put a link down below.
Alright, I'm just going to walk through this bit by bit because when you encounter this formula it seems just overwhelming.
But when you go through it one piece at a time, it's really not that complicated.
You can use this as a model to create your own loops.
Alright, So what we're going to do is we're going to pass to the slugify function the phrase that we want to see, and the number one.
Why the number one?
That's saying that we're going to do a loop for I = 1 to the length of the phrase.
Alright, so we're always starting at one.
It would be nice if in Lambda we could put the one there, but you know, simple enough for right now until they give us more Lambda tools, we're going to pass the one.
So we call slugify by passing a phrase and an index of 1.
Then first line of the Slugify is essentially like at the end of my formulas.
It is saying is the index - is that number one greater than the length of the phrase?
And if it is, then we're going to come down here and bail out.
But otherwise we're going to enter the loop.
So, this whole box here is the “We are Not Done Yet”.
We haven't processed all of the letters in the phrase.
And when that's true, we're going to call Slugify again.
And with this call of Slugify, we are going to pass it a different phrase.
We will talk about these six lines that calculate the phrase for the next call to Slugigy.
And this time the index is going to be one more than it was before.
So the first time we called slugify looking at character one.
When we're done looking at character one, we're going to pass it a 2.
So the index plus one.
Alright now.
Here's kind of what's happening inside that LET function.
This is where it's calculating the phrase that is going to send to Slugify the second time, the third time, the 4th time.
We get the character code using the CODE function.
Get everything to the left of the character.
And everything to the right of the character.
And then the heart of the Slugify - the thing that actually makes it work is this line right here.
We say, is the character a lowercase letter.
In other words, between 97 and 122 inclusive?
Or is it a digit - 48 to 57 inclusive?
If it is, then just put the character in the phrase, otherwise replace it with a dash.
Alright, so there you have it, a Lambda function that actually calls itself.
Looping through each character in any argument that we pass it.
Now, in order to actually make this work, we copy all of these characters from the Lambda function and go into Formulas, Define Name.
I've already done that.
So here in name Manager I created a name called Slugify.
I pasted the whole thing in there.
Click close And then just have anything.
[ Typing ] =SLUGIFY of that phrase comma one.
And will get rid of all the illegal characters.
Well hey, this examples is from my new book, MrExcel 2021 Unmasking Excel.
Click that I in the top right hand corner for more information about the book.
If you like this video please like subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Solving SLUGIFY with with recursive lambdas.
Yes, I am going to try and explain these recursive lambdas in plain English.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today an example from my friend Smozgur at the MrExcel Message Board.
There's a problem in web development.
Where you have to take an article title and convert it to HTML.
And that process is called Slugify. So they take an article title, convert to lowercase, keep anything that's a through Z or zero through 9.
And everything else becomes a dash.
But then at the end if you have consecutive dashes, you have to change it to a single dash.
And today I'm going to show you how we can solve this using something called a recursive Lambda.
That's where one of these new Lambda functions in Excel calls itself, which is crazy.
It's essentially doing a loop, like you would do in VBA.
And I'm going to show you step-by-step, in plain English how that works.
Before I do that I just want to note that this could have been done in Excel without Lambdas.
But it was super ugly.
Here's one where I take a phrase and character by character, splitting it out using the MID function.
Check to see what the character code is.
Is that character code between 97 and 122 or 48 and 57?
That's a through z and zero through 9.
Get everything before that character.
Get that character or a dash.
Get everything to the right of that character.
Put it back together.
And then increment the index by one.
Are we done?
Are we more than the length of G3?
And if we're not, then go back around.
Down here, sixty-four rows of formulas.
I finally get the end result.
But it's ridiculous to do it this way.
The other way is to say alright, there are 36 characters we want to keep.
Let's do SUBSTITUTE 219 times.
Here's all the different SUBSTITUTEs.
Getting rid of all the bad characters.
And then finally at the end, one more SUBSTITUTE where we do a TRIM of the SUBSTITUTE of every dash to a space.
That gets rid of the multiple dashes.
And then change it back to a dash.
Still not a fun way to go, right?
And we can solve this whole thing.
Any title over here in Column A.
Using the =slugify function that Smozgur wrote and posted in the great section of the MrExcel Message Board where people are posting their cool Lambdas.
I'll put a link down below.
Alright, I'm just going to walk through this bit by bit because when you encounter this formula it seems just overwhelming.
But when you go through it one piece at a time, it's really not that complicated.
You can use this as a model to create your own loops.
Alright, So what we're going to do is we're going to pass to the slugify function the phrase that we want to see, and the number one.
Why the number one?
That's saying that we're going to do a loop for I = 1 to the length of the phrase.
Alright, so we're always starting at one.
It would be nice if in Lambda we could put the one there, but you know, simple enough for right now until they give us more Lambda tools, we're going to pass the one.
So we call slugify by passing a phrase and an index of 1.
Then first line of the Slugify is essentially like at the end of my formulas.
It is saying is the index - is that number one greater than the length of the phrase?
And if it is, then we're going to come down here and bail out.
But otherwise we're going to enter the loop.
So, this whole box here is the “We are Not Done Yet”.
We haven't processed all of the letters in the phrase.
And when that's true, we're going to call Slugify again.
And with this call of Slugify, we are going to pass it a different phrase.
We will talk about these six lines that calculate the phrase for the next call to Slugigy.
And this time the index is going to be one more than it was before.
So the first time we called slugify looking at character one.
When we're done looking at character one, we're going to pass it a 2.
So the index plus one.
Alright now.
Here's kind of what's happening inside that LET function.
This is where it's calculating the phrase that is going to send to Slugify the second time, the third time, the 4th time.
We get the character code using the CODE function.
Get everything to the left of the character.
And everything to the right of the character.
And then the heart of the Slugify - the thing that actually makes it work is this line right here.
We say, is the character a lowercase letter.
In other words, between 97 and 122 inclusive?
Or is it a digit - 48 to 57 inclusive?
If it is, then just put the character in the phrase, otherwise replace it with a dash.
Alright, so there you have it, a Lambda function that actually calls itself.
Looping through each character in any argument that we pass it.
Now, in order to actually make this work, we copy all of these characters from the Lambda function and go into Formulas, Define Name.
I've already done that.
So here in name Manager I created a name called Slugify.
I pasted the whole thing in there.
Click close And then just have anything.
[ Typing ] =SLUGIFY of that phrase comma one.
And will get rid of all the illegal characters.
Well hey, this examples is from my new book, MrExcel 2021 Unmasking Excel.
Click that I in the top right hand corner for more information about the book.
If you like this video please like subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
