REDUCE Function in Excel (Extract Uppercase Words ONLY 🚀)

[@Softwaretrain]

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]

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]

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))

[@abbottkatz8830]

@@Computergaga Would BYROW have worked? TEXTSPLIT cannot natively handle arrays.

[@JoseAntonioMorato]

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]

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 😊

[@petercompton538]

Brilliant!

[@Computergaga]

Thank you, Peter.

[@ExcelWithChris]

Thank you!!! I see some South African surnames...... interesting.

[@Computergaga]

Oh yes! 🙂 Thank you, Chris. Hope you're doing well mate.

[@IvanCortinas_ES]

Great explanation Alan! Thanks for explaining these iterator functions.

[@Computergaga]

Thank you, Ivan.

[@xeeshanahmad8757]

Excellent Sir

[@Computergaga]

Thank you very much!

[@conradblume4274]

Excellent Video!

[@Computergaga]

Thank you, Conrad.

[@Mishkafofer]

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.

[@ahmetkaraaslan7880]

Hocam emeğinize sağlık Excelde uzman değilim Excel 2016 kullanıyorum 2016 versiyona göre formül ile yapabilimisiniz

[@Computergaga]

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.

[@nadermounir8228]

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]

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]

@@Computergaga ah ok got u

[@chandramohan1418]

these are not available for users of 365?

[@Computergaga]

Yes, they absolutely are. In-fact, they're only available for users of 365.