The ‘text-to-columns’ function enables you to separate the contents of one cell into multiple cells.
Microsoft Excel is a powerful tool that can be used not only for financial information but as a database. Many users rely upon Excel to store contact information and similar data, which can be readily sorted and rearranged. However, the functionality depends upon each data field being stored in its own Excel column.
If you have multiple fields stored in one Excel column, do not despair — the Excel “text-to-columns” function to the rescue! This command makes it easy to parse out the different fields and get them into separate cells where they belong.
Let’s say, for example, that one of your Excel columns has first and last name, and you need those separated out:
A1: George Washington
A2: John Adams
A3: Thomas Jefferson
A4: James Madison
A5: James Monroe
A6: John Quincy Adams
Follow these steps:
- Select the column to be parsed.
- Click on the Data menu, then Text-to-Columns.
- In Step 1 of the Wizard, choose whether your data is “delimited” (separated by characters such as spaces, commas or tabs) or “fixed with” (aligned in columns with spaces between the columns). Our example is delimited – there is only one space between each name.
- Click Next and go to Step 2 of the Wizard. Here you select the delimiter character – in this case a space. A preview at the bottom of the window shows you what your data is going to look like.
- Click Next and go to Step 3 of the Wizard. Here you can choose the data type of each column and skip columns. The default is “general,” which works in our example. Or we could choose “text.”
- You can also select where you want the data to go. By default, the parsed data will replace existing data in the current and adjacent cells. But you can designate another cell within the spreadsheet as the starting position for the parsed data, or insert columns so that data won’t be overwritten.
- Click Finish and voila! Your data is parsed into separate columns.
Now, in our example we have five records with two names but one record with three. “John Quincy Adams” will be parsed across three separate fields, and the last name won’t align with the others. But now that you have the text in separate columns, it’s a simple matter of inserting a column of blank cells within the first five records. Alternatively you could cut the first five last names and paste them into the next column.
What if you had a name with multiple words – say, Oscar de la Renta – that you didn’t want separated out. You can put quotation marks around “de la Renta” and Excel will treat it as a single field.