The information of a table is stored in the cells. How the information is organized can vary significantly. In the most common cases, a cell contains only a term or a number in order to be able to further process the data with precise cell references.
There are also cases where several pieces of information are stored in one cell. An example: We have a table where in each cell separated by commas there is a hotel name, postal code (ZIP code) and an assigned country one after the other (see first screen capture in the example below). Sorting the rows by zip code or country is not possible because there is only one column. To solve this problem, we split the contents of a cell in such a way that the individual pieces of information can be found in their own column. This is done with the Text in Columns function in Excel.
Use text in columns
First, we select the columns whose contents we want to split. Please note that usually the split contents are entered in the columns to the right of the original column. It is recommended to keep this area as free as possible, otherwise other contents will be overwritten in this area.
Next, on the Data tab in the Data Tools section, we click the Text in Columns button.
A wizard dialog will appear in a dialog box to help us with our project. First we decide whether we want to separate the columns by a fixed number of characters or by a certain criterion. Since our data are of different lengths and separated by a comma, we click on “Separated” and confirm with next.
Now we explain to Excel which characters separate the data. This can be several. In our example we put a check mark at “comma”, because our data was separated with a comma. Then we click on Next.Make sure that the contents do not contain any characters that cause unwanted separations.
In the next step we determine the formatting of the new columns. Here we click on the column (green arrow) and select the data format (red arrow above). In our example, the hotel names will have the data format “Text”. Clicking Next takes us to the next step.
And this is how the result looks like: One column became 3.