# SLUGIFY  	SLUGIFY function converts the given string to a URL slug.



## smozgur (Jan 22, 2021)

SLUGIFY function converts the given string to a URL slug.

We often use the document title as the page name to create a web page, which is called "URL slug". The document title often has invalid characters that cannot be used in the URL. Therefore, we generate a clean version of the title by replacing the invalid characters with a dash character. This function takes a given string and converts it to a URL slug by allowing only alphanumeric characters and dash.

This is a recursive function, and the second parameter is used as the loop counter.


```
=LAMBDA(reference, ndx,
  IF(ndx > LEN(reference),
    SUBSTITUTE(reference, "--", "-"),
    SLUGIFY(
      LET(
        character, LOWER(MID(reference, ndx, 1)),
        charcode, CODE(character),
        LEFT(reference, ndx - 1) & IF(OR(AND(charcode > 96, charcode < 123), AND(charcode > 47, charcode < 58)), character, "-") & RIGHT(reference, LEN(reference) - ndx)
      ),
      ndx + 1
    )
  )
)
```
SLUGIFY(reference, ndx)AB1Article TitleURL Slug2Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm3Searching in Online Book Catalogsearching-in-online-book-catalog4Pagination with Zend\Paginatorpagination-with-zend-paginator5Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table--zend-db6Model●View●Controller (MVC)model-view-controller-mvc-Sheet1Cell FormulasRangeFormulaB2:B6B2=SLUGIFY(A2,1)


----------



## Jon Peltier (Jan 22, 2021)

Now that's cool.


----------



## smozgur (Jan 22, 2021)

Jon Peltier said:


> Now that's cool.


Isn't it? I really love Lambda function!

The second parameter is actually only a counter, but Lambda doesn't support optional parameters which is actually not a big deal to implement. So it looks like we have to add it to the function to create a loop like this.
There is a suggestion in Excel.Uservoice that I also commented, but who knows when they will consider implementing that.





						UserVoice Pages
					






					excel.uservoice.com


----------



## Jon Peltier (Jan 22, 2021)

I suppose you could use a wrapper function SLUGIFY(A1) which called the real Lambda(reference, ndx) function


----------



## smozgur (Jan 22, 2021)

Jon Peltier said:


> I suppose you could use a wrapper function SLUGIFY(A1) which called the real Lambda(reference, ndx) function


You mean the following, right?


```
=LAMBDA(reference, SLUGIFY(reference, 1))(A1)
```

I didn't quite like that it requires an additional function definition, but it is actually a great solution until Microsoft allows optional parameters.


----------



## smozgur (Jan 24, 2021)

I originally wanted to create a recursive function to demonstrate a simple "for-next" implementation. However, it is also possible to create a non-recursive version. 
A slightly modified version that uses `validchar` variable for readability.


```
=LAMBDA(reference,
  TEXTJOIN("",
    TRUE,
    LET(
      character, LOWER(MID(reference,SEQUENCE(LEN(reference),,1),1)),
      charcode, CODE(character),
      validchar, (charcode > 96) * (charcode < 123) + (charcode > 47) * ( charcode < 58),
      IF(validchar, character, "-")
    ))
)(A2)
```

Note: We can't use AND/OR functions here since these functions will aggregate the results and return a single value instead of an array when they are used with an array function. So we should simply use boolean math, */+ equivalent for AND and OR respectively.


----------



## smozgur (Feb 5, 2021)

Posted a new Lambda function demonstrating how to create and use subroutines in a Lambda function.

In SLUGIFY.PLUS function, I am redirecting the process to a subroutine to clean the multiple adjacent dashes in the final slug by using an additional recursion instead of using the `SUBSTITUTE` function that actually cannot clean all possible adjacent dashes.









						SLUGIFY.PLUS
					

SLUGIFY.PLUS function is the advanced version of the SLUGIFY function I previously created.  We often use the document title as the page name to create a web page, which is called "URL slug". The document title often has invalid characters that cannot be used in the URL. Therefore, we generate a...




					www.mrexcel.com


----------



## MWANGI (Aug 5, 2022)

Interesting.

You can Simplify  LEFT(phrase, ndx-1) &  IF(OR(AND(charcode>97,charcode<123), AND(charcode>47,charcode<58)), char,  "-") & RIGHT(phrase, LEN(phrase)-ndx))       to Simply   IF((ABS(charcode-109.5)<=12.5)+(ABS(charcode-52.5)<=4.5), char,  REPLACE(phrase, ndx, 1, "-"))

Thanks for the non-recursive LAMBDA too!


----------



## Xlambda (Aug 5, 2022)

*Great recursive function smozgur !!* I think it was the very first one on this forum. ✌️
As much as I love recursive functions, nowadays, with the amazing new lambda helper functions, we can replace their functionality in even more efficient ways.
To be more exact, every recursive design based on a determined nr. of iterations beforehand (like nr. of chars in a string) can be replaced with a lambda helper function.
The only recursive functions that will survive are the ones on which the nr. of iterations is undermined and only when the exit condition is met, will deliver the result.
Even those can be tricked with helper functions, but they will never be as "elegant" as the recursive ones.

Back to the chase, here we need to identify digits and lower letters, which can be done with their character code values and comparison operations.
What if we need to also keep the upper letters, this implies even more comparison operations, no matter how much they can be simplified.
What if we need to keep any letter from whatever alphabet, we cannot rely on char codes anymore.
There is a simpler universal way to identify a letter from any alphabet, lower or upper case, which is:
*A letter is a character that has different values between its lower- and upper-case values.*
*On other words, for a letter "x" the formula =EXACT(LOWER(x),UPPER(x)) will return FALSE*
If returns TRUE could be a digit or a symbol. Digits are numbers. So, a symbol is a non-number and a non-letter.

Using this approach and replacing recursive functionality with MAP, here is an alternative function for entire column vector input data.
Also, the first variable after LET, "g", is the dash char "-"  ;   LET(g,"-", ......
If we need underscore char "_" we can change it with ease.
The function can handle any distribution of dash chars or any other distribution of symbols, located anywhere trough the strings, beginning, end, middle.

*AMSLG(a)* *A*rray *M*AP *SL*u*G*ify
*a*: column vector (1D vertical array)

```
=LAMBDA(a,
    LET(
        g, "-",
        m, MID(LOWER(a), SEQUENCE(, MAX(LEN(a))), 1),
        p, MAP(m, LAMBDA(x, OR(ISNUMBER(--x), NOT(EXACT(LOWER(x), UPPER(x)))))),
        i, IF(p, m, " "),
        b, BYROW(i, LAMBDA(x, CONCAT(x))),
        SUBSTITUTE(TRIM(b), " ", g)
    )
)
```
SLG.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ1AMSLG Concept21. extracting all chars of LOWER(a)3a=MID(LOWER(B4:B5),SEQUENCE(,MAX(LEN(B4:B5))),1)4--- /Aa2   --- (fgX \23-YZ )) ---- /aa2   --- (fgx \23-yz )) -5Aa2/@ \34|--- --6xY7 ))aa2/@ \34|--- --6xy7 ))672. identifing letters or digits 8=--MAP(D4#,LAMBDA(x,OR(ISNUMBER(--x),NOT(EXACT(LOWER(x),UPPER(x))))))9000001110000000011100110110000010111000011000000011110000000000011123. keeping letters and digits, the rest replaced with spaces " "13=IF(D9#,D4#," ")14     aa2        fgx  23 yz     15aa2    34       6xy7           16174. appending byrow18=BYROW(D14#,LAMBDA(x,CONCAT(x)))19     aa2        fgx  23 yz     20aa2    34       6xy7           21225. trimming and replacing spaces with dash char "-"23=SUBSTITUTE(TRIM(B19#)," ","-")24aa2-fgx-23-yz25aa2-34-6xy726AMSLGCell FormulasRangeFormulaD3,B23,B18,D13,D8D3=FORMULATEXT(D4)D4:AH5D4=MID(LOWER(B4:B5),SEQUENCE(,MAX(LEN(B4:B5))),1)D9:AH10D9=--MAP(D4#,LAMBDA(x,OR(ISNUMBER(--x),NOT(EXACT(LOWER(x),UPPER(x))))))D14:AH15D14=IF(D9#,D4#," ")B19:B20B19=BYROW(D14#,LAMBDA(x,CONCAT(x)))B24:B25B24=SUBSTITUTE(TRIM(B19#)," ","-")Dynamic array formulas.


----------



## smozgur (Aug 5, 2022)

Nice one, @Xlambda!   
Thanks for sharing!


----------



## smozgur (Jan 22, 2021)

SLUGIFY function converts the given string to a URL slug.

We often use the document title as the page name to create a web page, which is called "URL slug". The document title often has invalid characters that cannot be used in the URL. Therefore, we generate a clean version of the title by replacing the invalid characters with a dash character. This function takes a given string and converts it to a URL slug by allowing only alphanumeric characters and dash.

This is a recursive function, and the second parameter is used as the loop counter.


```
=LAMBDA(reference, ndx,
  IF(ndx > LEN(reference),
    SUBSTITUTE(reference, "--", "-"),
    SLUGIFY(
      LET(
        character, LOWER(MID(reference, ndx, 1)),
        charcode, CODE(character),
        LEFT(reference, ndx - 1) & IF(OR(AND(charcode > 96, charcode < 123), AND(charcode > 47, charcode < 58)), character, "-") & RIGHT(reference, LEN(reference) - ndx)
      ),
      ndx + 1
    )
  )
)
```
SLUGIFY(reference, ndx)AB1Article TitleURL Slug2Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm3Searching in Online Book Catalogsearching-in-online-book-catalog4Pagination with Zend\Paginatorpagination-with-zend-paginator5Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table--zend-db6Model●View●Controller (MVC)model-view-controller-mvc-Sheet1Cell FormulasRangeFormulaB2:B6B2=SLUGIFY(A2,1)


----------



## Xlambda (Aug 5, 2022)

SLG.xlsxABCDE12single cell formula3a=AMSLG(B4:B14)4Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm5Searching in Online Book Catalogsearching-in-online-book-catalog6Pagination with Zend\Paginatorpagination-with-zend-paginator7Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table-zend-db8Model●View●Controller (MVC)model-view-controller-mvc9Pagination / -/ /with Zend\\Paginatorpagination-with-zend-paginator10Searching in Online      Book     Catalogsearching-in-online-book-catalog11Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm12---- --Connected to Database Table - Zend\Db//connected-to-database-table-zend-db13Model●●●●●View●●●●●Controller (MVC)model-view-controller-mvc14AbC2----   ----   (34cd---12xYx---))))abc2-34cd-12xyx15AMSLG 1Cell FormulasRangeFormulaD3D3=FORMULATEXT(D4)D4:D14D4=AMSLG(B4:B14)Dynamic array formulas.


----------



## Xlambda (Aug 5, 2022)

smozgur said:


> Nice one, @Xlambda!
> Thanks for sharing!


Thank you !! ✌️🙏


----------



## MWANGI (Aug 6, 2022)

Xlambda said:


> *Great recursive function smozgur !!* I think it was the very first one on this forum. ✌️
> As much as I love recursive functions, nowadays, with the amazing new lambda helper functions, we can replace their functionality in even more efficient ways.
> To be more exact, every recursive design based on a determined nr. of iterations beforehand (like nr. of chars in a string) can be replaced with a lambda helper function.
> The only recursive functions that will survive are the ones on which the nr. of iterations is undermined and only when the exit condition is met, will deliver the result.
> ...


One of the best things I have learned today
 "
*A letter is a character that has different values between its lower- and upper-case values.*

"


----------

