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.
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:
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.
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.
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.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/text_to_columns.