Thank you Alan, alternatively, we can use the below formula: =LET( a,TEXTSPLIT(A2,," "), TEXTJOIN(" ",,FILTER(a,EXACT(a,UPPER(a))))) or if we want one formula without drag it down: =BYROW(A2:A15, LAMBDA(x,LET( a,TEXTSPLIT(x,," "), TEXTJOIN(" ",,FILTER(a,EXACT(a,UPPER(a)))))))
@Computergaga
5 ай бұрын
Thank you, buddy 👍 I did consider adding a BYROW to make it one formula in the video. But thought it a bit much. Plus it's based on a static range so felt it unnecessary. Nice use of FILTER.
@williamarthur4801
4 ай бұрын
Or just split by lower case, no numbers here; =TEXTJOIN(" ",TRUE,TEXTSPLIT(C3,CHAR(SEQUENCE(26,1,97,1) ))) there may be easier ways, but I thought of using REDUCE to extract numbers, upper or lower; F4 = Text and numbrs eg AbC133Ab22a = LET( alpha, REDUCE(F4, SEQUENCE(10,1,0,1),LAMBDA(x,y, SUBSTITUTE( x,y,"")) ), num, REDUCE(F4, MID( alpha,SEQUENCE(LEN(alpha)),1),LAMBDA(a,b, SUBSTITUTE( a,b,""))), upper, REDUCE( alpha, CHAR(SEQUENCE(26,1,97,1)),LAMBDA(c,d, SUBSTITUTE( c, d, "") )), lower, REDUCE( alpha, MID(upper, SEQUENCE(LEN(upper)),1),LAMBDA(e,f, SUBSTITUTE( e, f, "") )), SWITCH( H1, "justnumbers", num, "uppercase", upper, "lowercase", lower, "alltext", alpha))
@IvanCortinas_ES
5 ай бұрын
Great explanation Alan! Thanks for explaining these iterator functions.
@Computergaga
5 ай бұрын
Thank you, Ivan.
@conradblume4274
5 ай бұрын
Excellent Video!
@Computergaga
5 ай бұрын
Thank you, Conrad.
@petercompton538
5 ай бұрын
Brilliant!
@Computergaga
5 ай бұрын
Thank you, Peter.
@JoseAntonioMorato
5 ай бұрын
Dear Alan, Just to spill, without using the TRIM function: =BYROW(A2:A15,LAMBDA(a,REDUCE("",TEXTSPLIT(a," "),LAMBDA(acc,v,IF(EXACT(v,UPPER(v)),acc&v,acc))))) 🤗
@Computergaga
5 ай бұрын
Nice! Thanks, Jose Antonio. I did consider adding BYROW but decided there would not be much benefit with the static range as the source. Great to see the variations in how a task can be completed. Spoilt for choice nowadays 😊
@xeeshanahmad8757
5 ай бұрын
Excellent Sir
@Computergaga
5 ай бұрын
Thank you very much!
@ExcelWithChris
5 ай бұрын
Thank you!!! I see some South African surnames...... interesting.
Thank u Alan for this great video. In the true value of the of function. Can we just use V instead of acc” “&V ?
@Computergaga
5 ай бұрын
We will need both parameters Nader as one is the accumulated value, and the other is the current value. So for example, in the name Anna Bella DE LA CRUZ, by the time the REDUCE function loops to the last word, the 'acc' parameter contains 'DE LA' and the 'v' parameter contains 'CRUZ'.
@nadermounir8228
5 ай бұрын
@@Computergaga ah ok got u
@ahmetkaraaslan7880
4 ай бұрын
Hocam emeğinize sağlık Excelde uzman değilim Excel 2016 kullanıyorum 2016 versiyona göre formül ile yapabilimisiniz
@Computergaga
3 ай бұрын
You're welcome! In Excel 2016, you're best looking at a Power Query approach. I'm not sure on a formula for that version.
@Mishkafofer
4 ай бұрын
What is interesting is that it basically a looping inside Excel. Until Dynamic Array, i presume it only could have been done only in VBA.
@chandramohan1418
5 ай бұрын
these are not available for users of 365?
@Computergaga
5 ай бұрын
Yes, they absolutely are. In-fact, they're only available for users of 365.
Пікірлер: 23