Dynamic Text to Columns (📄to📅)

In today's article, we are going to see different ways to do text to columns which are more dynamic and easier than the default "Text to Column" option in Data Tab.

1️⃣Regular Text to Column Option (Data Tab)

Before moving to new dynamic options available for Text to Columns, let us look at how the regular Text to Column (traditional) options work.

For Instance, below are the list of sales details given in a combined format which we have to convert into a data table for better analysis.

Steps:

1. Select the data (which we have to convert into columns)

2. Navigate to the Data tab » Text to Columns (under the Data Tools group)

3. Delimited » insert “ comma (,) ” in others box as delimiter » Next & Finish

Final Result 📋

Drawbacks 👎🏻

1. Static in nature, if we add more data or modify the existing data this option will not dynamically update the same according to the current criteria.

2. Lengthy Process, this option involves a multi-level process for specifying the delimiter.

2️⃣ Power Query

Steps:

1. Select the whole data » and convert it into the table (Ctrl + T)

2. Navigate to the Data tab » From Table/Range (under Get & Transform Data group)

3. In the Power Query editor » Split Columns by (under Home tab & Transform group) » By Delimiter

4. Select Comma as Delimiter and press OK

5. Change the Column Headings

6. Navigate to Close and Load to » Select the destination location for loading the table

Final Result 📋

Advantages 👍🏻

1. Works Dynamically when adding or modifying the data in the original table. After adding or modifying the existing data, hit the refresh option (or) Alt + F5 in the Splitted data table it will fetch you the updated data containing the modified data.

2. Initial Lengthy Process and subsequent easy access to the functionality, this option may consume some time in setting up the initial query but can be easily accessed thereafter by just hitting the refresh option.

3️⃣ TEXTSPLIT Function (ƒ)

This function is available only for Microsoft 365 users and Excel Web.

Steps:

1. First syntax: Select the cell in which original data is present

2. Second syntax: Specify the delimiter in this case comma (,)

3. Drag the formula to all the cells

Final Result 📋

Advantages 👍🏻

1. Easy to use, since the above option involves only the function it’s easier to use compared to other options mentioned above.

2. Not Dynamic, but can be dragged for the addition of data to the original table.