— 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