Text to Columns || Learn @ ExcelSuite

Explanation

Text to Columns is a useful tool that allows the user to split a certain column into multiple columns using a delimiter.

Text to Columns can also be used to quickly convert date formats (from American to European and vise versa) and also text to numbers (or numbers to text).

Example

Let’s see how we can split a column that contains values separated by commas:

We can access the Text to Columns tool from the Data ribbon, then selecting “Delimited”, and finally – selecting which delimiter will be used for splitting the column:

Using Text to Columns to convert American Date Format to European Date Format (and vice versa)

We can use Text to Columns to quickly change the date format of a certain column.

To do so, follow the next steps:

1. Go to Text to Columns
2. Select “Delimited” then click “Next”
3. Do not choose any delimiter, and click “Next”
4. In step 3 we will determine the desired output column format. Select “Date”, and choose the current format of the date (MDY):

Let’s see how it works if we want to convert American dates to European dates (Assuming our default date format is European):

Amazing, right? 

Using Text to Columns to convert Text to Values (and vice versa)

Did you know that you can quickly change data stored as text to values (and vice versa) using Text to Columns?

This is extremely useful when you need to use a function like VLOOKUP, but you find out that while your lookup value is stored as a number, the column in which you search is stored as text. 

In this case, we can do the following:

1. Go to Text to Columns
2. Select “Delimited” then click “Next”
3. Do not choose any delimiter, and click “Next”
4. In step 3 we will determine the desired output column format. If we want to change a column stored as text to values, we will select “General” to convert the numbers stored as text to a number (“General”) format:

In case we want to convert values stored as numbers to text, we can just select the “Text” option instead of “General”.

Practice Text to Columns

Now, let’s practice Text to Columns, including some of its advanced features.