Microsoft Excel Tutorial: Building a Slugify function with recursive LAMBDA functions in Excel.

Welcome to episode 2390 of the MrExcel Podcast, where we tackle the problem of SLUGIFY with recursive lambdas. In this video, I, Bill Jelen, will be explaining these recursive lambdas in plain English, so even if you're not familiar with them, you'll be able to follow along.

The inspiration for this episode comes from my friend Smozgur at the MrExcel Message Board. He brought to my attention a common problem in web development where article titles need to be converted to HTML. This process is called Slugify, and it involves converting the title to lowercase, keeping only letters and numbers, and replacing any other characters with dashes. However, the tricky part is dealing with consecutive dashes, which need to be replaced with a single dash.

Before we get started, I want to mention that this solution was shared by Smozgur on the MrExcel Message Board, where people are posting their cool lambda functions. I'll leave a link to the thread in the description below. Now, let's take a look at the problem and how we can solve it using a recursive lambda. We'll start by looking at two alternative methods that are not as efficient or elegant as the lambda solution. Then, I'll walk you through the lambda function step-by-step, so you can use it as a model to create your own loops in Excel.

The first alternative method involves using a series of formulas to split the phrase character by character, check for the character code, and replace any unwanted characters with dashes. This method requires 64 rows of formulas and is not very practical. The second alternative method involves using the SUBSTITUTE function 219 times to get rid of all the bad characters, and then using TRIM and SUBSTITUTE again to handle the consecutive dashes. While this method is more efficient than the first one, it is still not ideal.

Now, let's take a look at the recursive lambda solution. We will pass the phrase and the number one to the slugify function, which will act as our loop. The first line of the function checks if the index is greater than the length of the phrase. If it is, the function will stop and return the final result. Otherwise, it will call itself again, passing a different phrase and an index that is one more than the previous one.

Inside the LET function, we calculate the phrase that will be passed to the slugify function in the next iteration. This is where the magic happens. We use the CODE function to get the character code, and then check if it is a lowercase letter or a digit. If it is, we keep the character, otherwise, we replace it with a dash. This process continues until the index is greater than the length of the phrase, and then the final result is returned.

To make this lambda function work, we need to copy the code and create a named range in Excel. I have already done this and named the range "Slugify". Now, we can use this function by typing "=SLUGIFY(phrase, 1)" in any cell. This will return the slugified version of the phrase, with all the illegal characters replaced by dashes.

I hope this video has helped you understand how to use recursive lambdas in Excel.Don't forget to leave your questions and comments in the YouTube comments section below. Thank you for watching, and I'll see you in the next MrExcel netcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #lambda #slugify

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

Thanks to Smozgur who posted this example at the MrExcel Message Board LAMBDA forum: https://www.mrexcel.com/board/forums/...

This video answers these common search terms:
how to make a lambda function on excel
what is a lambda function in excel
what is lambda function in excel
how to create lambda function excel
how to use lambda in excel
excel how to use lambda
how do i use excel's lambda function.
How to create slugs from titles
Slugify vs. URL encoding
Slugify special characters
Slugify for URLs in Excel using LAMBDA
Slugify for SEO optimization
Slugify and clean URLs

Table of Contents
(0:00) Slugify function with recursive LAMBDA
(0:22) Convert article title to URL using Slugify
(1:00) Solution without LAMBDA requires many formulas
(1:43) Using SUBSTITUTE 219 times
(2:03) Using a Recursive LAMBDA
(2:45) Looping in a LAMBDA
(3:38) Calling Slugify again
(4:03) Code inside of LET function
(5:24) Clicking Like really helps with the algorithm