smozgur
BatCoder
- Joined
- Feb 28, 2002
- Messages
- 2,656
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
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.
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 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Article Title | Slug | ||
| 2 | Pagination with Zend\\Paginator | pagination-with-zend-paginator | ||
| 3 | Searching in Online Book Catalog | searching-in-online-book-catalog | ||
| 4 | Start Coding with Doctrine©2 ORM | start-coding-with-doctrine-2-orm | ||
| 5 | Model Mapper Connected to Database Table - Zend\Db | model-mapper-connected-to-database-table-zend-db | ||
| 6 | Model●●●●●View●●●●●Controller (MVC) | model-view-controller-mvc | ||
Sheet2 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B6 | B2 | =SLUGIFY.PLUS(A2,1,1) |
Upvote
1
