Cleaning up data and Counting words

Hi,

Welcome to the forum

Excel has many tool to clean up data, like Text to Columns (to split data into multiple columns), remove duplicates, and functions to evaluate and clean up text. Unfortunately, variations of the same word can be complex to fix as the PC uses logic and not assumptions to analyse data. It is possible to clean up the data but if the source data is a lot and there are multiple variations, there is no guarantee that the data can to cleansed 100%.

If you can supply a small sample of your data (desensitized) and provide instructions, we can assist you in scrubbing it as best possible and setting it up for further analysis...

You can upload a sample by clicking the Go Advanced button on the bottom right and using the paperclip button in the advanced window( on the toolbars) to upload a sample file...

As an additional note... Pivot Tables can do the counting of words for you, BUT the structure of your data will need to be prepared and cleansed to get any form of accurate and effective feedback.

Ok... Looking into it....

Hi,

The data is in a mess...wow!!
I used Find/Replace and Text to Columns to do initial cleanup and then split.
Then I placed all the words into a single column
The Pivot uses the single A column as a source to group and count words.

You can continue the cleanup process in column A by using spell check and Find/Replace to fine-tune the counting in the Pivot.
Just remember to right click on the Pivot Table and select Refresh to keep it up-to-date.

After using text to columns, which split the data into several columns, I set up a small macro that removed all the blank cells and compacted the data in each column, and then it transferred each block within the column to the bottom of the A column (appending the data to the bottom). The macro just looped this same action several times until all the columns were empty.

I hate doing things manually