create function put date value in written language to avoid maximum number of lines in formula

[littlepete]

hello all

my formula to represent all data in my address list, has reached the maximum of 8192 ...
i would like to replace the normal formula with a short function to reduce that number...
Is it possible to make something like this : a function "txt" to transform the data to written tekst
(25/12/2020 => friday, 25 december 2020)
in the formula it would look like :
TEKST(Q5;"dddd d mmmm jjjj") => txt(q5)

thanks for your advice all !!!

 

[RoryA]

Which version of Excel do you have? If it's 365 you could use a LAMBDA.

 

[littlepete]

Which version of Excel do you have? If it's 365 you could use a LAMBDA.

hello

i'm using indeed office 365 ... so what's a lambda ?

thx !

 

[RoryA]

If you have the LAMBDA function, then see the MS article here for usage: LAMBDA function

 

[littlepete]

If you have the LAMBDA function, then see the MS article here for usage: LAMBDA function

hello

in the meanwhile i had read already about the lambda function, but i see nowhere where i should put that definition of what lambda does...

part of my formula now looks like :

Q5&"was born on "&TEKST(Q5;"dddd d mmmm jjjj")

how should i change that part to (much) shorter line to gain lines in total? someting like Lambda(q5) ?

 

[RoryA]

The article I linked to has a step-by-step guide; where did you get stuck?

 

[littlepete]

The article I linked to has a step-by-step guide; where did you get stuck?

hello
i have no idea how i should form the lambda? something like lambda(date, TEKST("dddd d mmmm jjjj"), q5) ... ? or ?

 

[RoryA]

Replace q5 with date and yes, that's basically it. (though presumably you need semicolons not commas)

 

[littlepete]

Replace q5 with date and yes, that's basically it. (though presumably you need semicolons not commas)

hello

at the end, i'm trying to reduce the number of characters in my long (over 9000 ch.) formula...
i thought making something like txt(q5) that would do TEKST(Q5;"dddd d mmmm jjjj")
that would reduce 28 characters tot 7 ! but with lambda i only see more characters ?
what's then the best way ?
btw: thx for your time !!!

 

[RoryA]

How are you seeing more characters? What you describe is exactly what the LAMBDA is for. Define a name called txt using your LAMBDA formula, then use txt(q5) in your formulas.