Splitting columns

For a certain period of time, when I need to split data in a column, my first choice is to use the Power Query add-in and the Split Columns option. However, there are still users who do not know how to use it, so I remembered that for this purpose we can also use the Excel option Text to Columns. In this “recipe” you will find out a practical way how to split one column into several.

We will start the example with a simple table that contains first and last name of employees (IME I PREZIME). Select this data, without the header, and then find and run the Text to Columns option in the Data ribbon. This will start a wizard that will take you through the process of splitting the column in several steps.

In the first step, you choose the Delimited or Fixed Width option. The first is used if we break the columns on the basis of some character that separates the words in the cell. It can be a comma, a semicolon, or some other character. The second option is used if we want to break the column into several columns based on a fixed number of characters. Select the Delimited option and click Next.

In the second step, we choose the character that separates the words in the cells- delimiter, and on the basis of which we will separate the columns. In this example we will choose Space.

The last step serves to check once again whether we have prepared the process of splitting one column into several. Here we can specify the type of data to which the data in each newly created column will belong. By clicking on Finish, we finish the process of separating the columns.

In the end, it is only necessary to change the header titles of the newly created columns.