Is there a formula for excel to identify what are the commonly text identified in excel?

[liabilityquek]

Hi Guys

(1) I have a set of keywords and would like to know whether is there a way to identify the commonly used keywords? I have tried using Index,mode and match but it returns #N/A.

(2) Would it be possible if i could rank the commonly used keywords?

Thanks in advance

 

[GraH - Guido]

If you have Power Query on board (Excel 2010 onwards on Windows machine, as of 2016 "onboard" on the ribbon)
It's rather easy.

1. On the data ribbon select Get & Transform Data -> From Table/file
2. Add a Custome Column and Type the formula = Text.Split([keyword]," ")
3. Then Expand this list (click arrows pointing up and to the side)
4. On the transformation tab click Group By (default counting is okay)
5. Sort Descending
6. Save and load to Excel

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"keyword", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "All Words", each Text.Split([keyword]," ")),
    #"Expanded All Words" = Table.ExpandListColumn(#"Added Custom", "All Words"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All Words",{"keyword"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"All Words"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}})
in
    #"Sorted Rows"

 

[liabilityquek]

Sorry, when i enter in the formula = text.split([keyword]," "); an error was displayed. "Expression.Error: The name 'text.split' wasn't recognized. Make sure it's spelled correctly." Would you be able to advise?

 

[GraH - Guido]

Ensure to use the proper case as M-language is case sensitive. It must be Text.Split.

 

[liabilityquek]

Ensure to use the proper case as M-language is case sensitive. It must be Text.Split.

Thanks! Managed to get it work.