SLUGIFY

=SLUGIFY(reference, ndx)

reference
Required. The cell reference (or string value) that contains the string to be converted to a URL slug.
ndx
Required. Starting character index. Should be entered as 1 to convert the entire string.

SLUGIFY function converts the given string to a URL slug.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,765
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.

Excel Formula:
=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)
AB
1Article TitleURL Slug
2Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm
3Searching in Online Book Catalogsearching-in-online-book-catalog
4Pagination with Zend\Paginatorpagination-with-zend-paginator
5Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table--zend-db
6Model●View●Controller (MVC)model-view-controller-mvc-
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=SLUGIFY(A2,1)
 
Upvote 0

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
570
Office Version
  1. 365
Platform
  1. Windows
SLG.xlsx
ABCDE
1
2single cell formula
3a=AMSLG(B4:B14)
4Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm
5Searching in Online Book Catalogsearching-in-online-book-catalog
6Pagination with Zend\Paginatorpagination-with-zend-paginator
7Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table-zend-db
8Model●View●Controller (MVC)model-view-controller-mvc
9Pagination / -/ /with Zend\\Paginatorpagination-with-zend-paginator
10Searching in Online Book Catalogsearching-in-online-book-catalog
11Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm
12---- --Connected to Database Table - Zend\Db//connected-to-database-table-zend-db
13Model●●●●●View●●●●●Controller (MVC)model-view-controller-mvc
14AbC2---- ---- (34cd---12xYx---))))abc2-34cd-12xyx
15
AMSLG 1
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D4)
D4:D14D4=AMSLG(B4:B14)
Dynamic array formulas.
 

MWANGI

New Member
Joined
May 8, 2014
Messages
27
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) Array MAP SLuGify
a: column vector (1D vertical array)
Excel Formula:
=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.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1AMSLG Concept
21. 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 ))
6
72. identifing letters or digits
8=--MAP(D4#,LAMBDA(x,OR(ISNUMBER(--x),NOT(EXACT(LOWER(x),UPPER(x))))))
90000011100000000111001101100000
101110000110000000111100000000000
11
123. keeping letters and digits, the rest replaced with spaces " "
13=IF(D9#,D4#," ")
14 aa2 fgx 23 yz
15aa2 34 6xy7
16
174. appending byrow
18=BYROW(D14#,LAMBDA(x,CONCAT(x)))
19 aa2 fgx 23 yz
20aa2 34 6xy7
21
225. trimming and replacing spaces with dash char "-"
23=SUBSTITUTE(TRIM(B19#)," ","-")
24aa2-fgx-23-yz
25aa2-34-6xy7
26
AMSLG
Cell Formulas
RangeFormula
D3,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.
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.

"
 

Forum statistics

Threads
1,171,185
Messages
5,874,230
Members
433,039
Latest member
Izzy2091

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top