Excel questions at the Financial Modelling Summit 2022

Slugify text

We want to slugify text, ie include only characters a-z, 0-9 and “-“, so that it can become part of an URL. So that for example

“Why I say ‘Bazinga!” – my #1 Reason for Using =INDEX(MATCH())!!”



Bill Jelen’s MrExcel site has a LAMBDA function which achieves the effect through recursion:

=LAMBDA(reference, ndx,
  IF(ndx > LEN(reference),
    SUBSTITUTE(reference, “–“, “-“),
        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

I didn’t like the idea of using recursion where simple looping through the string would do, so I wrote this Slugify(text) user-defined function using a LAMBDA. It converts the text to lowercase and then to an array, iterates through all the characters in the array passing them to another LAMBDA function AlphaNum(char) to return a space if the character is not a-z or 0-9, and finally concatenates the array back to a string.

Slugify =LAMBDA(text, SUBSTITUTE( TRIM(  CONCAT( AlphaNum( MID(LOWER(text),SEQUENCE(LEN(text)),1))))," ","-")) 

AlphaNum =LAMBDA(char,IF((char>="a")*(char<="z")+(char>="0")*(char<="9"),char," "))

We cannot define AlphaNum as

=LAMBDA(char,IF( OR( AND(char>=”a”, char<=”z”), AND(char>=”0″, char<=”9″)),char,” “))

Because AND() and OR() return single values and cannot be used in an array function. So I use Boolean * for AND and + for OR.

Bill Jelen showed a neat trick to reduce multiple occurrences of a character: change all occurrences of that character to a space, then use TRIM to reduce all spaces to one, then substitute back. Here I use spaces to begin with, then substitute them with a dash at the end.

Of course, the only use for LAMBDA is to create a nice encapsulated user-defined function. If you want a non-LAMBDA formula on a direct cell reference eg A3, just use a simple LET:

IF((ch>="0")*(ch<="9")+(ch>="a")*(ch<="z"),ch," "))
))," ","-")

IFS() functions only work with ranges, not arrays

A participant asked:

Why does this work: =LET(A,A1:A10,B,A+1,COUNTIFS(A,”<5″)); it returns a count.

But this does not: =LET(A,A1:A10,B,A+1,COUNTIFS(B,”<5″)); it returns #VALUE!.

The reason is that all the IFS functions only accept a range as their first parameter, not an array. So for example =COUNTIFS({1,2,3,4,5,6},”<5”) gives a syntax error, an invalid formula.

The solution is to use FILTER() to select the values you want, then use COUNT or AVERAGE or whatever you want on that.


Patrick O’Beirne, 7 October 2022


About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s