Excel BI Challenge Workout 004

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 4 —

Provide a formula to Extract the Words which are all capitals (upper case).
Hence if a word is “HAROLD Benison THOMAS” then answer would be “HAROLD THOMAS”

(Post answers in Comment)

Download Practice File - https://lnkd.in/d_nkmXHP

#excel, #advancedexcel, #excelchallenge, #excelproblem, #excelquestion, #excelsolution, #excelformulas, #excelfunctions, #exceltips, #exceltricks

Excel BI’s LinkedIn Post:

Just for my own learning, here’s an OfficeScript / TypeScript approach.

function main(wb: ExcelScript.Workbook) {
  const rng = wb.getFirstWorksheet().getRangeByIndexes(1, 0, 9, 1)
  const keepCaps: (number | string) [][] = rng.getValues().map(x => x.toString().match(/\b[A-Z]+\b/g) || []);
  const restult: string [] = keepCaps.map(x => x.join(" "))
  console.log(keepCaps)
}

Here is one way out with Power Query.

let
	Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Return = Table.AddColumn (
		Source,
		"Answer",
		each [
			Split   = Text.Split ( [Words], " " ),
			Select  = List.Select ( Split, ( f ) => f = Text.Upper ( f ) ),
			Combine = Text.Combine ( Select, " " )
		][Combine]
	)
in
	Return

Find Words All Capitals.xlsx (21.4 KB)

=MAP(A2:A10,LAMBDA(x,LET(y,TEXTSPLIT(x," "),u,UPPER(y),t,EXACT(y,u),IFERROR(TEXTJOIN(" ",1,FILTER(u,t)),""))))

=DROP(REDUCE(" ",A2:A10,LAMBDA(x,y,LET(a,TEXTSPLIT(y," "),u,UPPER(a),t,EXACT(a,u),VSTACK(x,IFERROR(TEXTJOIN(" ",1,FILTER(u,t)),""))))),1)

Find Words All Capitals - Quadri Solution.xlsx (20.9 KB)

`

Here is one way out with Excel.

=MAP(
    A2:A10,
    LAMBDA(a,
        LET(
            \s, TEXTSPLIT(a, " "),
            \c, IF(EXACT(\s, UPPER(\s)), \s, ""),
            \r, TEXTJOIN(" ", 1, \c),
            \r
        )
    )
)

Here:
s = Split
c = Condition if the text is upper
r = Return

Find Words All Capitals.xlsx (21.4 KB)

LinkedIn Post by:
Rick Rothstein

I don’t have TEXTSPLIT in my copy of XL365, so I had to use what the LET function is assigning to N instead; so, if you have TEXTSPLIT, I am guessing you can substitute it for what I had to use and shorten the formula a little bit. Anyway, with that said, this will return all upper case “words” in the text…

=TEXTJOIN(" ",1,LET(N,TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",99)),SEQUENCE(6)*99,99)),IF(EXACT(N,UPPER(N)),N,"")))

Note: I assumed no full name would never have more than 6 names in it. If you think it could be more, just change the 6 inside the SEQUENCE function to that number.

LinkedIn Post by:
Tolga Demirci

=TEXTJOIN(;;IFERROR(IF(IF(UPPER(MID($A2;ROW($A$1:$A$100);1))<>" ";FIND(TEXT(MID($A2;ROW($A$1:$A$100);1);"?");UPPER(MID($A2;ROW($A$1:$A$100);1));1);"");TEXT(MID($A2;ROW($A$1:$A$100);1);"?");"");""))

LinkedIn Post by
Sergei Baklan

Another Power Query variant

let
  Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  UppercaseWords = Table.AddColumn(
    #"Promoted Headers",
    "Uppercase Words",
    each
      [
        w = Text.Split([Words], " "),
        u = Text.Split( Text.Upper( [Words] ), " "),
        c = Text.Combine(
          List.RemoveMatchingItems( w,
          List.RemoveMatchingItems( w, u ) ),
          " ")
      ][c], type text )
in
  UppercaseWords

LinkedIn Post by
محمد حلمي

Frist attempt
=TRIM(TEXTJOIN(" ";;IFERROR(IF(CODE(IFERROR(MID(E3;IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));50);IFERROR(SMALL(IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));100);ROW($2:$20));100)-IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));50));""))<97;IFERROR(MID(E3;IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));50);IFERROR(SMALL(IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));100);ROW($2:$20));100)-IFERROR(UNIQUE(SEARCH(" ";" "&E3;ROW($1:$50)));50));"");"");"")))

LinkedIn Post by:
Muhammad Waqas Khan

well due to some organization restriction I was unable to download the file so I am assuming data is starting from A2 cell which need a formula or vba UDF
here is my formula

=IFERROR(TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '')='']")),"")

LinkedIn Post by:
Aditya Kumar Darak

Here is a Power Query Way:

let
   Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
   Split = Table.AddColumn ( Source, "Split", each Text.Split ( [Words], " " ) ),
   Select = Table.AddColumn (
      Split,
      "Select",
      each List.Select ( [Split], ( f ) => f = Text.Upper ( f ) )
   ),
   Result = Table.AddColumn ( Select, "Result", each Text.Combine ( [Select], " " ) ),
   Final = Table.SelectColumns ( Result, { "Words", "Result" } )
in
   Final

LinkedIn Post by:
Aditya Kumar Darak

There is another one with records.

let
   Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
   Calculations = Table.AddColumn (
      Source,
      "Records",
      each [
         a = Text.Split ( [Words], " " ),
         b = List.Select ( a, ( f ) => f = Text.Upper ( f ) ),
         c = Text.Combine ( b, " " )
      ]
   ),
   Return = Table.ExpandRecordColumn ( Calculations, "Records", { "c" }, { "Result" } )
in
   Return

LinkedIn Post by:
Aditya Kumar Darak

Now, I am getting more clarity about the codes now.

let
  Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  Function = ( f as text ) =>
    let
      a = Text.Split ( f, " " ),
      b = List.Select ( a, ( x ) => x = Text.Upper ( x ) ),
      c = Text.Combine ( b, " " )
    in
      c,
  Return = Table.AddColumn ( Source, "Result", each Function ( [Words] ) )
in
  Return

LinkedIn Post by
Bhavya Gupta

Solution Using Power Query -

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Words", "Words - Copy"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Words - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Words - Copy"),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Only Capital", each if [#"Words - Copy"] = Text.Upper([#"Words - Copy"]) then [#"Words - Copy"] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Words - Copy"}),
#"Grouped Rows" = [Table.Group](http://table.group/)(#"Removed Columns", {"Words"}, {{"All", each Text.Combine ([Only Capital]," "), type nullable text}})
in
#"Grouped Rows"

LinkedIn Post by
Daniel Madhadha

=TEXTJOIN(" ",TRUE,IF(EXACT(TEXTSPLIT(A2," "),UPPER(TEXTSPLIT(A2," "))),UPPER(TEXTSPLIT(A2," ")),""))

happy to start the Map function

=MAP(A2:A10,LAMBDA(a,LET(b,TEXTSPLIT(a," "),TEXTJOIN(" ",,IF(EXACT(b,UPPER(b)),UPPER(b),"")))))

LinkedIn Post by:
Udit Kumar Chatterjee

Here is my solution in hashtag#PowerQuery

let
Source = #"Challenge-04",
addReqColumn = Table.AddColumn(
Source,
"Uppercase Words",
each
Text.Replace(
Text.Trim(
Text.Combine(List.Transform(Text.Split([Words], " "), each if _ = Text.Upper(_) then _ else ""),
" ")
),
" ",
" "
),
type text
)
in
addReqColumn

LinkedIn Post by
John Jairo V.

Hi to all!
Another option could be:
=LET(t,TEXTSPLIT(A2," "),TEXTJOIN(" ",,REPT(t,EXACT(t,UPPER(t)))))
Blessings!

LinkedIn Post by:
Bhavya Gupta

=LET(a,TEXTSPLIT(A2," "),TEXTJOIN(" ",TRUE,FILTER(a,EXACT(a,UPPER(a)),"")))

LinkedIn Post by
Rick Rothstein

As I said in my previous post, I do not have the TEXTSPLIT function available to me, so I cannot test this formula; however, the syntax seems simple enough so, if I understand what I read correctly, then this modification to the formula I posted early seems like it should work (hopefully someone will test it for me)…

=TEXTJOIN(" ",1,LET(N,TEXTSPLIT(A1," "),IF(EXACT(N,UPPER(N)),N,"")))

LinkedIn Post by:
Hakeem Lawrence, MSBA

Here is my Power Query Solution:

let
Source = Sheet1,
Remove_Column = Table.RemoveColumns(Source,{"Column2"}), // Remove Answer column
Promote_Headers = Table.PromoteHeaders(Remove_Column, [PromoteAllScalars=true]), // Promote first row to header
Extracted = Table.AddColumn(Promote_Headers, "Answers", each [List.Select](http://list.select/)(Text.Split([Words], " "), each _ = Text.Upper(_))), // Add Column with lists where values from words column are uppercased
Result = Table.TransformColumns(Extracted, {"Answers", each Text.Combine(List.Transform(_, Text.From), " "), type text}) // Extract values
in
Result