Excel has a built-in function called Text to Column to parse information into separate columns from a single column. We're going to use a spreadsheet with data listed in three columns. The first column contains data for city, state, and zip code. (See Figure 22.1)
Use this workbook for the chapter.
Note: This is the same workbook used in the Text Functions chapter.
However, we want to separate the information from this single column into individual columns. To achieve this, we use Excel’s Text to Column function. To get started, navigate to the Data tab in the ribbon toolbar. Then, press the Text to Columns button. (See Figure 22.2)
Note: We can use functions to separate data, but Text to Columns can be useful when the data does not need to update after separation.
Using a Delimiter for Text to Column
The Text to Columns button will open a dialogue box with two options as shown in Figure 22.3: Delimited and Fixed Width. Delimited parses data by using specific symbols as separators to represent the point data will be divided. Fixed Width parses data determined by the number of spaces between characters specified for separation.
We’re going to look at the Delimited option first, then we’ll address Fixed Width. Complete the following steps:
- Select Delimited and press Next. (See Figure 22.3)
- The window will ask what we want to be the delimiter. We can choose tabs, semicolon , comma, space, or another specific character of our choice. (See Figure 22.4)
- Look at what separates the city, state, and zip code.
- A comma separates the city and state, then a double space separates the zip code.
- Select Comma to separate the city data.
- Notice the preview demonstrates where the separation will occur and it will remove the comma.
- Press Next to continue.
- Determine how the separated data is formatted.
- Note: The Do not import column (skip) option can be selected to simply remove excess data.
- For this example, we’ll select General to port the excess data to the next cell.
- Press Finish to complete the function. (See Figure 22.5)
Choosing a Delimiter
Now that we have separated the city data, we need to do the same for state and zip code because they are divided by two blank spaces instead of a comma. However, attempting to separate state and zip code by a space delimiter could pose a problem with state names containing a space. For example, New York would be divided with New and York in their own columns.
One solution would be to replace the double spaces with a valid delimiter. We can do this by using the Find & Replace tool.
- Navigate to the Home tab of the ribbon toolbar and press the Find & Select button (See Figure 22.6)
- Select Replace….
- The Find and Replace window will prompt you to type the characters to search, then the character to replace the found selection.
- Enter two blank spaces in the Find what field
- Enter a “pipe” or vertical bar ( | ) in the Replace with field.
- Press Find Next and Replace for each instance, or press Replace All to complete the action for every instance. (See Figure 22.7)
Once completed, the Text to Column function can be used again to separate the data by the vertical bar delimiter as it did with the comma.
Using Fixed Width for Text to Column
The Fixed Width option will separate data based on the selected space between characters. For example, we could select the eighth space on the ruler and everything after will be separated into the new cell(s). This option can be useful if all the data equals the same number of characters, such as a five-digit zip code (Ssee Figure 22.8), but we only need the first two digits.