Find certain words (2/3/4/5 words) in a paragraph

[aimkmb]

HI,
I would like a formula that could find certain exact words ( 2 - 5 words) in a paragraph, and returns with values.
Eg 3 exact words found in a paragraph, return values = 3

thanks....

 

[James006]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

Hi,

What is for you ... a paragraph ???

Are you working with Excel ... or with Word ...???

 

[aimkmb]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

Hi,

What is for you ... a paragraph ???

Are you working with Excel ... or with Word ...???

excel. short paragraph / sentence containing aroung 10 - 15 words..

 

[sandy666]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

is that what you want?
(with single sentence)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]sentence[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]I would like a formula that could find certain exact words ( 2 - 5 words) in a paragraph, and returns with values[/td][/tr]
[/table]
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]words[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]find certain exact words[/td][/tr]
[/table]
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

5​

[/td][/tr]
[/table]

5 because words is twice

 

[aimkmb]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

yes exactly! would you mind share how to do the formula...thanks!

 

[sandy666]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

sure but PowerQuery is required

for source table

[SIZE=1]let
    CharsToReplace = List.Transform({33..64,91..96,123..126}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"sentence", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "sentence"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "TT", each Text.Trim([sentence],CharsToReplace)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([TT] <> ""))
in
    #"Filtered Rows"[/SIZE]

for words list table

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"words", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "words")
in
    #"Split Column by Delimiter"[/SIZE]

for result table

[SIZE=1]let
    Source = Table.NestedJoin(Table8,{"words"},Table7,{"TT"},"Table7",JoinKind.LeftOuter),
    #"Expanded Table7" = Table.ExpandTableColumn(Source, "Table7", {"TT"}, {"TT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table7", each ([TT] <> null)),
    #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[TT]),
    #"Converted to Table" = #table(1, {{#"Calculated Count"}})
in
    #"Converted to Table"[/SIZE]

 

[aimkmb]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

sure but PowerQuery is required

for source table

[SIZE=1]let
    CharsToReplace = List.Transform({33..64,91..96,123..126}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"sentence", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "sentence"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "TT", each Text.Trim([sentence],CharsToReplace)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([TT] <> ""))
in
    #"Filtered Rows"[/SIZE]

for words list table

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"words", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "words")
in
    #"Split Column by Delimiter"[/SIZE]

for result table

[SIZE=1]let
    Source = Table.NestedJoin(Table8,{"words"},Table7,{"TT"},"Table7",JoinKind.LeftOuter),
    #"Expanded Table7" = Table.ExpandTableColumn(Source, "Table7", {"TT"}, {"TT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table7", each ([TT] <> null)),
    #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[TT]),
    #"Converted to Table" = #table(1, {{#"Calculated Count"}})
in
    #"Converted to Table"[/SIZE]

Thanks for sharing this useful info! ?

 

[sandy666]

Re: Help! Find certain words (2/3/4/5 words) in a paragraph

You are welcome

btw. don't quote whole post