The Text to Columns tool is particularly worth discussing further, as it is highly useful and often overlooked by even longtime Excel users.
As any analyst knows very well, data isn’t always presented in the most ideal formats for use in Excel. Data may come from a variety of robust data storage sources, or even from such unlikely places as Word documents!
Here is an Example of How the Text to Columns Tool Works:
To illustrate, we will use the following string of numbers (intentionally rudimentary…) that you may find in a Word document or any other numerous sources:
14, 22, 36, 35, 64, 34, 28, 94
- Select the string, copy it, and paste it into a cell in Excel (in this example, A2 was used)
- Select the cell and click on the Text to Columns icon on the DATA ribbon
- The Convert Text to Columns Wizard will appear giving you the following two major options:
- Delimited – Characters such as commas separate each field
- Fixed width – Fields are aligned in columns with spaces between each field
- In our example, we choose Delimited since our numbers are separated by commas
- Following the steps in the Text to Columns Wizard gives you the choice to pick your desired format:
It’s as Simple as That! It really is that easy to get your data into Excel in proper alignment and format. Have no doubt about it, Text to Columns is a very worthwhile gadget for any Excel user to have in their tool belt. Give it a Try!