Ok, this is one that had been bugging me for a while and I’ve finally got around to spending some time to figure out how to do this. Quite boringly, this is actually for creating a WordCloud from Google Analytics data, but hey. It has nice visuals and is a great way of visualising data. Firstly, hats off to people like Jason Davies for creating awesome tools like this for people to use. What a lot of the better WordCloud tools available online allow you to do is to create WordClouds with phrases, not simply just single words. Single word WordClouds can be handy, but they can often lose the context. So personally I was looking for something that could keep things grouped by phrases. Specifically I was looking to turn Google Analytics data, which is in the format of Data | Number of Times, into a WordCloud. On smaller data sets, this is relatively straight forward as you can simply manipulate the data in Excel and copy and paste data a number of times to get the required result. When data sets get large though, this solution soon becomes difficult. And here comes more Excel magic that allows you to manipulate Excel data into the format you need, in this case….
Data from;
- Name | Number of Times (i.e. Web Developer Job in Manchester | 50
Data into;
- Web Developer Job in Manchester
- Web Developer Job in Manchester
- Web Developer Job in Manchester
- Etc.
And the beauty of Excel, this is possible with a carefully crafted formula! Whoop!
The simple answer for how to do this is, with A1 being the ‘Name’ in the example above and B1 being the ‘Number of Times’ in the example above;
=REPT(CONCATENATE(A1, CHAR(10)), B1)
Then to break this down a little for those who aren’t too familiar with Excel, here’s what all this means;
- REPT: This function quite simply is for repetition whereby the function is, REPT(text, number_times).
- Text Required. The text you want to repeat.
-
Number_times Required. A positive number specifying the number of times to repeat text.
- CONCATENATE: This is simply joining two or more things together, i.e. CONCATENATE(“This”, “With”, “This”), which would result in, This With This
- CHAR(10): This is the formula for inserting a specific character into a formula, in this case a New Line Break character
And that is it. This magical formula formats all of the data into what is required. When you are viewing this data in Excel, it won’t add the data into a new Cell, so simply copy and paste the whole data into another text document and you’ll see the data on new lines as you expect.
Pretty cool! Well I thought so anyhow 🙂
Michael Cropper