SLUGIFY.PLUS

=SLUGIFY.PLUS(reference, ndx, subroutine)

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. This parameter can be also considered as the "loop counter".
subroutine
Required. The subroutine index of the process that we want to call. Simply using integer values to separate the subroutines and the initial value is 1 in this function.

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

smozgur

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

The difference from the original version is the SLUGIFY.PLUS removes any multiple adjacent dashes generated in the final slug. This logic requires an additional recursion, and in the SLUGIFY.PLUS function, I demonstrate how to call different subroutines in a single LAMBDA function.

The function logic is separated by using the IF conditional at the beginning of the function and executing different processes according to the subroutine value. When all the characters are cleaned, instead of using a single SUBSTITUTE function as in the original version, SLUGIFY.PLUS function calls itself by using passing 2 as the subroutine parameter value. When the subroutine parameter is 2, the ndx parameter is irrelevant. However, since LAMBDA does not support optional arguments, I am still passing a 0 as the ndx parameter.

As a bonus, the dashes at the beginning and the ending of the slug are also cleared.

This is a recursive function, the second parameter is used as the loop counter, and the last parameter is used as the subroutine selector to call the inline process to clear the adjacent dashes in the final slug.

Excel Formula:
=LAMBDA(reference,ndx,subroutine,
  IF(subroutine=1,
    IF(ndx > LEN(reference),
      SLUGIFY.PLUS(reference, 0,2),
      SLUGIFY.PLUS(
        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,
        1
      )
    ),
    IF(LEN(reference)-LEN(SUBSTITUTE(reference, "--", "")) = 0,
      LET(
        clearleft, IF(LEFT(reference, 1)="-", RIGHT(reference, LEN(reference)-1), reference),
        clearright,  IF(RIGHT(clearleft, 1)="-", LEFT(clearleft, LEN(clearleft)-1), clearleft),
        clearright
      ),
      SLUGIFY.PLUS(
        SUBSTITUTE(reference, "--", "-"), 0, 2
      )
    )
  )
)
WIP.xlsm
AB
1Article TitleSlug
2Pagination with Zend\\Paginatorpagination-with-zend-paginator
3Searching in Online Book Catalogsearching-in-online-book-catalog
4Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-orm
5Model Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table-zend-db
6Model●●●●●View●●●●●Controller (MVC)model-view-controller-mvc
Sheet2
Cell Formulas
RangeFormula
B2:B6B2=SLUGIFY.PLUS(A2,1,1)
 
Upvote 1
I have always wanted to use regex in Excel. Although it is possible in VBA using an extra library and through a custom function, I always thought it should be a native function in Excel. There is still not an Excel regex function obviously.

However, now there is PY! I know it is still extra work but kind of a native way without using VBA. Here is the Python function using regex as an additional solution to the slugify feature.

Python:
import re
rng = xl("A1:A6", headers=True)
def slugify(s):
    s = re.sub(r"[^a-zA-Z0-9]", "-", s.lower())
    s = re.sub(r"[-]{2,}", "-", s)
    s = re.sub(r"^[-]|[-]$", "", s)
    return s
rng["Slug"] = rng["Article Title"].apply(slugify)
rng[["Slug"]]

Note: Now we have Python in Excel, I should implement a BBCode option on the post editor for the Python code snippets!
 
Hi Suat, Py news is Huge. Implementing it here will be fabulous. Excel opened the Big Front Door to AI. AI fits to Py like a glove. Excel, the most universal platform, IDE and GUI in one place.
The fact that it's cloud-based means that it can run even faster than locally for big data sets.
Lambda's "programming" syntax it's too complicated for AI so far, too niched, in the bad way, compared with the huge amount object.methods libraries in Py.
I think Py will take over and all the other methods will become obsolete.
 
When lambda started, I was enthusiastic about it, I thought, everybody will use custom made ones, content creators will embrace it, will be libraries...etc.
Nothing of that happened. This is the only place where some real interest manifested.
I am considering diving into Py all in, publishing whatever is already done (a lot) and abandoning the lambda boat.
What do you think about the matter? Time is expensive and should be invested with care.
 
Hi @Xlambda,

VB/A is kind of my first love after years of GW Basic and Fortran on DOS. So, I will always feel like cheating, but on the other side, Python is always very powerful compared to VBA. It is also a huge move encapsulating the entire application logic in a cell as a formula. I should say that I am really impressed this time (except for the last paragraph below). So, although I don't think VBA will go away anytime soon, Python might be really famous and preferred by programmers to create native solutions in Excel.

Regarding AI, I am still not feeling very comfortable using AI programmatically (even though I created some skills during the initial release of Amazon Echo years ago, it is of course not compared to today's AI tools), however, I agree with you about AI and Python.

I am not very sure about the cloud-based implementation. I believe we should be free to implement our applications by using whatever library/module is available, not only with what is preloaded on the server. And the "security" should not be Microsoft's concern at this point. I can easily destroy my computer by using simple VBA codes, so I don't get it. I understand Microsoft is trying to make another $ubscription path for this, but we should be also allowed to use our local Python compiler and also work with files on our computer. Otherwise, this will be only another tool that we will play around with, and hope users to quit VBA and use Python-based applications. I feel that it won't happen if PY is used as announced.

Finally, the biggest improvement could be only by replacing (or even better, adding) VBE with a new "PYE". Then it could be amazing. But we all know that it won't happen.
 
Interesting. Thank you for your thoughts. ✌️🙏
Speaking of lambdas, do you think that this one solves all the aspects of SLUGIFY and SLUGIFY.PLUS ? I do not know if I am missing something, some functionality.
I have used all type of chars, even emojis, other alphabets letters not only regular ones on a 2D array.
SLUGIFY(ar)
ar: any array, vector or 2D

Excel Formula:
=LAMBDA(ar,
    LET(
        f, LAMBDA(x, LET(m, MID(LOWER(x), SEQUENCE(LEN(x)), 1), TRIM(CONCAT(IF(ISNUMBER(--m) - EXACT(LOWER(m), UPPER(m)) + 1, m, " "))))),
        SUBSTITUTE(MAP(ar, f), " ", "-")
    )
)
SLG.xlsx
ABCDEFG
1
2=SLUGIFY(B3:C25)
3ÀÁÂ---!!\\\\ÃÄÅÆÇÈÉÊË8 76jkh876$%& ñññÑÑÑÑ----&&&ÀÁÂ---!!\\\\ÃÄ--== ¿¿ÅË8 76jkh876$%& ññÑÑÑ----àáâ-ãäåæçèéêë8-76jkh876-ñññññññàáâ-ãä-åë8-76jkh876-ñññññ
4((Start Coding with Doctrine©2 ORM--Start Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-ormstart-coding-with-doctrine-2-orm
5//Searching in Online //Book //Catalog\\\Searching in Online Book Catalogsearching-in-online-book-catalogsearching-in-online-book-catalog
6Pagination with Zend\PaginatorPagination with Zend\Paginatorpagination-with-zend-paginatorpagination-with-zend-paginator
7Model Mapper Connected to Database Table - Zend\DbModel Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table-zend-dbmodel-mapper-connected-to-database-table-zend-db
8---Model●View●Controller (MVC)---Model●View●Controller (MVC)model-view-controller-mvcmodel-view-controller-mvc
9Pagination / -/ /with Zend\\PaginatorPagination / -/ /with Zend\\Paginatorpagination-with-zend-paginatorpagination-with-zend-paginator
10Searching in Online Book CatalogSearching in Online Book Catalogsearching-in-online-book-catalogsearching-in-online-book-catalog
11Start Coding with Doctrine©2 ORMStart Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-ormstart-coding-with-doctrine-2-orm
12---- --Connected to Database Table - Zend\Db//---- --Connected to Database Table - Zend\Db//connected-to-database-table-zend-dbconnected-to-database-table-zend-db
13Model●●●●●View●●●●●Controller (MVC)Model●●●●●View●●●●●Controller (MVC)model-view-controller-mvcmodel-view-controller-mvc
14AbC2----😉🙏🤼‍♀️🤼‍♀️ ---- (34cd---12xYx---))))AbC2---- ---- (34cd---12xYx---))))abc2-34cd-12xyxabc2-34cd-12xyx
15Start Coding with Doctrine©2 ORMStart Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-ormstart-coding-with-doctrine-2-orm
16Searching in Online Book CatalogSearching in Online Book Catalogsearching-in-online-book-catalogsearching-in-online-book-catalog
17Pagination with Zend\PaginatorPagination with Zend\Paginatorpagination-with-zend-paginatorpagination-with-zend-paginator
18Model Mapper Connected to Database Table - Zend\DbModel Mapper Connected to Database Table - Zend\Dbmodel-mapper-connected-to-database-table-zend-dbmodel-mapper-connected-to-database-table-zend-db
19Model●View●Controller (MVC)Model●View●Controller (MVC)model-view-controller-mvcmodel-view-controller-mvc
20Pagination / -/ /with Zend\\PaginatorPagination / -/ /with Zend\\Paginatorpagination-with-zend-paginatorpagination-with-zend-paginator
21Searching in Online Book CatalogSearching in Online Book Catalogsearching-in-online-book-catalogsearching-in-online-book-catalog
22Start Coding with Doctrine©2 ORMStart Coding with Doctrine©2 ORMstart-coding-with-doctrine-2-ormstart-coding-with-doctrine-2-orm
23---- --Connected to Database Table - Zend\Db//---- --Connected to Database Table - Zend\Db//connected-to-database-table-zend-dbconnected-to-database-table-zend-db
24Model●●●●●View●●●●●Controller (MVC)Model●●●●●View●●●●●Controller (MVC)model-view-controller-mvcmodel-view-controller-mvc
25AbC2---- weuh9873ttr--__uy ---- (34cd---12xYx---))))ººº|||€€€AbC2---- ---- (34cd---12xYx---))))abc2-weuh9873ttr-uy-34cd-12xyxabc2-34cd-12xyx
26
SLUGIFY
Cell Formulas
RangeFormula
E2E2=FORMULATEXT(E3)
E3:F25E3=SLUGIFY(B3:C25)
Dynamic array formulas.
 
Speaking of lambdas, do you think that this one solves all the aspects of SLUGIFY and SLUGIFY.PLUS ?
Since the sample I posted above is only looking for English chars, it doesn't generate the same result for the first entry, cell B3, but all the others are covered.
The first regex pattern can be changed to the following instead of looking for a-bA-Z0-9 only:
Python:
s = re.sub(r"[^\w]", "-", s.lower())
But this one will also include the underscore character as well. One more pattern change will fix this:
Python:
s = re.sub(r"[\W_]", "-", s.lower())

That's why I love regex, it is a clean-up monster when it is used properly. Updated formula with some comments:
Python:
import re # Need to import regex module
rng = xl("B2:B25", headers=True) # Range that contains "Article Title" header
def slugify(s):                          # The Slugify function to be applied to the dataframe
    s = re.sub(r"[\W_]", "-", s.lower()) # Replace all non-alphanumeric chars including underscore, also lower the string
    s = re.sub(r"[-]{2,}", "-", s)       # Replace all occurence of double dashes, so make sure to keep single dash
    s = re.sub(r"^[-]|[-]$", "", s)      # Remove the leading and trailing dashes - if any
    return s                             # Return the slugified string
rng["Slug"] = rng["Article Title"].apply(slugify)    # Create a new series called Slug
rng[["Slug"]]    # Return the Slug series as a dataframe
 
So , if we are offline, it does the job. 😉
 

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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
Back
Top