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())!!”

Becomes

“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, “–“, “-“),     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     )   ) )

https://www.mrexcel.com/board/threads/slugify.1159292/

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:

=SUBSTITUTE(TRIM(CONCAT( LET(ch,MID(LOWER(A3),SEQUENCE(LEN(A3)),1), 
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.

=LET(A,A1:A10,B,A+1,AVERAGE(FILTER(B,B<5)))

Patrick O’Beirne, 7 October 2022

Advertisement