• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

Well-Known Member
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
    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}})
    #"Sorted Rows"


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?
Last edited by a moderator: