You know how some times you receive raw data in word and you really need to analyze it but you can’t do it in the format that was emailed to you. You have the option of taking this raw data and coping and pasting it onto an Excel spreadsheet, but it will probably dump the pasted data on one column. And you can’t analyze it like that. The solution is to split the raw data into columns. Many people don’t know about this handy trick that lets you split data from column into several other columns. In this short tutorial, I will show you the easy steps to get this done.
- Lets pretend that you have copied a massive bulk of information from a website. You copied cities, states and university names. But when you copied and pasted the data, all the information got dumped in one column in an Excel spreadsheet. And you want to separate the cities, states and university names into three separate columns. To accomplish this, first, select all the raw data, in this case, the column, and go to Data>Data Tools>Text to Columns, and now you will work with the “Convert Text to Columns” wizard.
- Next, you need to click on either “Delimited” or “Fixed With” under the heading “Choose the file type that best describes your data” – if you’re dealing with text data with similar formatting, then “Delimited” works better but if you’re dealing with numbers “Fixed With” may work better. And click Next.
- Now, you need to tell the wizard how to split the data and this will depend on whether you selected “Delimited” or “Fixed With.” If you clicked on “Delimited” you need to specify the symbol that separates the data, in most cases dealing with formatted text is a comma. But if you picked “Fixed With” then you can use the ruler that is provided to split the data by clicking and dropping the check point in the specific areas where you wan the data to be split. Also, you can see a preview of how the data will look like in “Data preview.”
- Finally, hit the Finish button and click OK. Now you will see the data nicely organized into separate columns. Now you can add headings to each column and format your new table accordingly.
Great Job! Using this highly ignored but powerful function in Excel, you’ll be able to organzie and analyze raw data in no time and look really smart in front of your coworkers.