Word frequency

How often do repeat certain words in the given text? Which one of them is most often repeated? The answer to this question can be obtained using a few simple techniques, which will be more talk in the text that follows. To begin with, a news post is taken from the link http://www.iib.rs/srl/bizit2018-srl/and we will analyse it to measure the frequency of words occurrence. The selected text should be copied to cell A1 of the new worksheet.

The first step is to position the cell A1 and then select the Text to Columns option from the Data ribbon. Convert Text to Columns Wizard will open. In the first window, select the Delimited option value, and then click Next. In the following window, all punctuation marks and the space character must be selected. By clicking Finish, the first part of the process ends with the result of all the words that appear in the text, listed in the first row of the worksheet.

The next step requires selecting the whole first row, copying the content to Clipboard, and then using the transpose option from Paste Special menu, after which all values in first row are moved to the column, starting with cell A3. In cell A2, you need to enter the text “REČI” (WORDS), and then delete the entire first row. Select cell A2 and all other values in the first column and convert them to a structured table (CTRL + T). If you did everything right, the result is a table with one column in which there is a list of all the words that have occurrence within a text.

The last step is to create a pivot table from the structured table we made. The “REČI” (WORDS) field should be dragged into Rows and Values. If we now sort the table in descending order, according to the frequency, we will see which word is most often repeated in the text.

The idea for this text was given to me by wife of my colleague, Ljubica Kiš (thanks Ljubica). I invite you all to write me and suggest texts that contain solutions of Excel problems that bother you.