List the words where second word starts with C. 5 second words start with C and are highlighted in Yellow.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 12
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Second Words Start with C with Power Query
Power Query solution 1 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.AddColumn(
Source,
"Calculation",
each Text.StartsWith(Text.Split([Names], " "){1}, "C")
),
Result = Table.SelectRows(Calculation, each ([Calculation] = true))[[Names]]
in
ResultPower Query solution 2 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.AddColumn(
Source,
"Calculation",
each Text.StartsWith(Text.AfterDelimiter([Names], " "), "C")
),
Result = Table.SelectRows(Calculation, each ([Calculation] = true))[[Names]]
in
ResultPower Query solution 3 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.AddColumn(
Source,
"Calculation",
each Text.At([Names], Text.PositionOf([Names], " ") + 1) = "C"
),
Result = Table.SelectRows(Calculation, each ([Calculation] = true))[[Names]]
in
ResultPower Query solution 4 for Second Words Start with C, proposed by Brian Julius:
let
Source = #"People Raw",
#"Split Column by Delimiter" = Table.SplitColumn(
Source,
"Names",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
{"Names.1", "Names.2"}
),
#"Filtered Rows" = Table.SelectRows(
#"Split Column by Delimiter",
each Text.StartsWith([Names.2], "C")
),
#"Merged Columns" = Table.CombineColumns(
#"Filtered Rows",
{"Names.1", "Names.2"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
"Names"
)
in
#"Merged Columns"Power Query solution 5 for Second Words Start with C, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Filter1 = Table.SelectRows(Source, each Text.Start(Text.AfterDelimiter([Names], " "), 1) = "C"),
Filter2 = Table.SelectRows(
Source,
each Text.StartsWith(Text.AfterDelimiter([Names], " "), "C", Comparer.OrdinalIgnoreCase) = true
)
in
Filter2Power Query solution 6 for Second Words Start with C, proposed by Zbigniew Szyszkowski:
let
Source = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each Text.StartsWith(Text.Split([Names], " "){1}, "C")
)
in
#"Filtered Rows"Power Query solution 7 for Second Words Start with C, proposed by Shubham Vashisht:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(
Source,
"Names",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Names.1", "Names.2", "Names.3"}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"Names.1", type text}, {"Names.2", type text}, {"Names.3", type text}}
),
#"Capitalized Each Word" = Table.TransformColumns(
#"Changed Type",
{{"Names.2", Text.Proper, type text}}
),
#"Filtered Rows" = Table.SelectRows(
#"Capitalized Each Word",
each Text.StartsWith([Names.2], "C")
),
#"Merged Columns" = Table.CombineColumns(
#"Filtered Rows",
{"Names.1", "Names.2", "Names.3"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
"Expected Answer"
)
in
#"Merged Columns"Power Query solution 8 for Second Words Start with C, proposed by Bohdan Duda, PhD:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Inserted Text After Delimiter" = Table.AddColumn(
Source,
"Result",
each
if Text.Middle([Names], Text.Length(Text.BeforeDelimiter([Names], " ")) + 1, 1) = "C" then
[Names]
else
null,
type text
),
#"Filtered Rows" = Table.SelectRows(#"Inserted Text After Delimiter", each ([Result] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Result"})
in
#"Removed Other Columns"Power Query solution 9 for Second Words Start with C, proposed by Bankim Ghosh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Names", "Names - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Duplicated Column",
"Names - Copy",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Names - Copy.1", "Names - Copy.2", "Names - Copy.3"}
),
#"Added Custom" = Table.AddColumn(
#"Split Column by Delimiter",
"Names.1",
each if Text.StartsWith([#"Names - Copy.2"], "C") then [#"Names - Copy.2"] else null
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Names.1] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Names"})
in
#"Removed Other Columns"Solving the challenge of Second Words Start with C with Excel
Excel solution 1 for Second Words Start with C, proposed by Rick Rothstein:
=LET(
R,
A2:A20,
FILTER(
R,
FIND(
" ",
R&"1 "
)=FIND(
" C",
R&" C"
),
)
)
Note that the above formula protects against a cell having a single name in it (such as Cher). If you know that all cells will always have a minimum of two names in them,
then you can use this formula instead...
=LET(
R,
A2:A20,
FILTER(
R,
FIND(
" ",
R
)=FIND(
" C",
R&" C"
),
)
)Excel solution 2 for Second Words Start with C, proposed by محمد حلمي:
=LET(
d;
A2:A20;
FILTER(
d;
MID(
d;
SEARCH(
" ";
d
)+1;
1
)="c"
)
)Excel solution 3 for Second Words Start with C, proposed by محمد حلمي:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
)
)="C"
)Excel solution 4 for Second Words Start with C, proposed by 🇰🇷 Taeyong Shin:
=TOCOL(
REGEXEXTRACT(
A2:A20,
"^w+ C.*(?: .*)?",
,
1
),
2
)Excel solution 5 for Second Words Start with C, proposed by Julian Poeltl:
=LET(
N,
A2:A20,
FILTER(
N,
MAP(
N,
LAMBDA(
A,
LEFT(
CHOOSECOLS(
TEXTSPLIT(
A,
" "
),
2
),
1
)="C"
)
)
)
)Excel solution 6 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A20,
IFERROR(
FIND(
" C",
A2:A20
) = FIND(
" ",
A2:A20
),
FALSE
)
)Excel solution 7 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A20,
EXACT(
LEFT(
REPLACE(
A2:A20,
1,
FIND(
" ",
A2:A20
),
""
)
),
"C"
)
)Excel solution 8 for Second Words Start with C, proposed by Hussein SATOUR:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
)
) = "c"
)Excel solution 9 for Second Words Start with C, proposed by Abdallah Ally:
=FILTER(
A2:A20,
MID(
A2:A20,
FIND(
" ",
A2:A20,
1
)+1,
1
)="C"
)Excel solution 10 for Second Words Start with C, proposed by Bhavya Gupta:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
),
1
)="C"
)Excel solution 11 for Second Words Start with C, proposed by Charles Roldan:
=LAMBDA(
f,
LAMBDA(
x,
FILTER(
x,
f(
x
)
)
)
)(LAMBDA(
f,
LAMBDA(
x,
MAP(
x,
f
)
)
)(LAMBDA(
f,
f(
f
)
)(LAMBDA(f,
LAMBDA(x,
IF(LEFT(
x
) = " ",
MID(
x,
2,
1
) = "C",
f(
f
)(REPLACE(
x,
1,
1,
))))))))(A2:A20)Excel solution 12 for Second Words Start with C, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
={IFERROR(
INDIRECT(
"A"&SMALL(
IF(
MID(
$A$2:$A$20,
FIND(
" ",
TRIM(
$A$2:$A$20
)
)+1,
1
)="C",
ROW(
$A$2:$A$20
),
""
),
ROW(
A1
)
)
),
""
)Excel solution 13 for Second Words Start with C, proposed by CA Raghunath Gundi:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" ",
1,
1,
0
),
1
)="C"
)Excel solution 14 for Second Words Start with C, proposed by Jardiel Euflázio:
=FILTER(
A2:A20,
REPLACE(
A2:A20,
SEARCH(
" ",
A2:A20
)+1,
1,
"C"
) = A2:A20
)Excel solution 15 for Second Words Start with C, proposed by Jardiel Euflázio:
=FILTER(
A2:A20,
MID(
A2:A20,
SEARCH(
" ",
A2:A20
)+1,
1
)="C"
)Excel solution 16 for Second Words Start with C, proposed by Jardiel Euflázio:
=FILTER(
A2:A20,
SEARCH(
" ",
A2:A20
)=IFERROR(
SEARCH(
" C",
A2:A20
),
0
)
)Excel solution 17 for Second Words Start with C, proposed by Jardiel Euflázio:
=FILTER(
A2:A20,
LEFT(
SUBSTITUTE(
A2:A20,
TEXTSPLIT(
A2:A20,
" "
),
""
),
2
)=" C"
)Excel solution 18 for Second Words Start with C, proposed by Victor Momoh (MVP, MOS, R.Eng):
=FILTER(
A2:A20,
LEFT(
REPLACE(
A2:A20,
1,
SEARCH(
" ",
A2:A20
),
""
),
1
)="C"
)Excel solution 19 for Second Words Start with C, proposed by Mahmoud Bani Asadi:
=TOCOL(
IF(
BYROW(
A2:A20,
LAMBDA(
x,
MID(
x,
FIND(
" ",
x
)+1,
1
)
)
)="C",
A2:A20,
NA()
),
2
)Excel solution 20 for Second Words Start with C, proposed by Mahmoud Bani Asadi:
=FILTER(
A2:A20,
MID(
SUBSTITUTE(
A2:A20,
TEXTSPLIT(
A2:A20,
" "
),
""
),
2,
1
)="C"
)Excel solution 21 for Second Words Start with C, proposed by Sergei Baklan:
=TOCOL(
IF(
& LEFT(
TEXTAFTER(
Names,
" ",
1
)
) = "C",
Names,
NA()
),
2
)Excel solution 22 for Second Words Start with C, proposed by Cary Ballard, DML:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
)
) = "C"
)Excel solution 23 for Second Words Start with C, proposed by RIJESH T.:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" ",
1
)
)="c"
)Excel solution 24 for Second Words Start with C, proposed by Miguel Angel Franco García:
=LET(
a;
SI(
IZQUIERDA(
EXTRAE(
A2:A20;
ENCONTRAR(
" ";
A2:A20
)+1;
LARGO(
A2:A20
)-ENCONTRAR(
" ";
A2:A20
)
)
)="C";
A2:A20;
""
);
FILTRAR(
a;
a<>""
)
)Excel solution 25 for Second Words Start with C, proposed by Amr Tawfik CMA®,FMVA,Lean Coach:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
),
1
)="C"
)Excel solution 26 for Second Words Start with C, proposed by Amr Tawfik CMA®,FMVA,Lean Coach:
=FILTER(
A2:A20,
LEFT(
MAP(
A2:A20,
LAMBDA(
x,
IFERROR(
TEXTBEFORE(
TEXTAFTER(
x,
" "
),
" "
),
TEXTAFTER(
x,
" "
)
)
)
),
1
)="C"
)Excel solution 27 for Second Words Start with C, proposed by Nazmul Islam Jobair:
=FILTER(
A2:A20,
LEFT(
TEXTAFTER(
A2:A20,
" "
),
1
)="C"
)Excel solution 28 for Second Words Start with C, proposed by Thiago da Silva Romeiro:
=UNIQUE(
IF(
SEARCH(
" ",
A2:A20,
1
)=SEARCH(
" C",
A2:A20,
1
);A2:A20
),
,
TRUE
)Excel solution 29 for Second Words Start with C, proposed by Thiago da Silva Romeiro:
=UNIQUE(
XLOOKUP(
IF(TEXTAFTER(A2:A20&"-"&(SEARCH(
" C",
A2:A20,
1
)=SEARCH(
" ",
A2:A20,
1
)=TRUE),
"-")="TRUE",
A2:A20,
""),
IF(TEXTAFTER(A2:A20&"-"&(SEARCH(
" C",
A2:A20,
1
)=SEARCH(
" ",
A2:A20,
1
)=TRUE),
"-")="TRUE",
A2:A20,
""),
A2:A20)
,
,
TRUE)Excel solution 30 for Second Words Start with C, proposed by Thiago da Silva Romeiro:
=UNIQUE(
XLOOKUP(
IF(
SEARCH(
" ",
A2:A20,
1
)=SEARCH(
" C",
A2:A20,
1
),
A2:A20
),
IF(
SEARCH(
" ",
A2:A20,
1
)=SEARCH(
" C",
A2:A20,
1
),
A2:A20
),
A2:A20
)
,
,
TRUE
)Excel solution 31 for Second Words Start with C, proposed by Md niaz Islam:
=FILTER(A2:A20,
(MID(
A2:A20,
SEARCH(
" ",
A2:A20,
1
),
2
)=" c"))Excel solution 32 for Second Words Start with C, proposed by John Gatonye:
=FILTER(
A2:A20,
MID(
A2:A20,
FIND(
" ",
A2:A20
)+1,
1
)="C"
)Excel solution 33 for Second Words Start with C, proposed by Dawid Sebastian Gałęzyka:
=LET(
vRange,
A2:A20,
v2WStart,
FIND(
" ",
vRange
),
FILTER(
vRange,
MID(
vRange,
v2WStart+1,
1
)="C"
)
)Solving the challenge of Second Words Start with C with Python in Excel
Python in Excel solution 1 for Second Words Start with C, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:A20", True)
result = [i for i in data["Names"] if i.split()[1][0] == "C"]
result
Solving the challenge of Second Words Start with C with DAX
DAX solution 1 for Second Words Start with C, proposed by Zoran Milokanović:
EVALUATE
FILTER(Input,
VAR N = Input[Names]
VAR F = FIND(" ", N, 1, 0)
VAR C = MID(N, F + 1, 1)
RETURN
F > 0 && C = "C"
)