Spreading data across Columns in Excel Without VBA Code

.

In my previous article as you learnt how to split data using VBA SPLIT() function. In this Article I am going to explain you how to separate the data across columns of a cell without excel VBA. Microsoft Excel has a built-in function called Text-to-Columns under Data ribbon (refer the Pic 1). Using this option user can split the data of a cell across columns which are joined by a specific delimiter or fixed length.

How to use Text to Columns Built-in Function of Excel ?

Text to Columns function is easy to use. I am going to make you expert in Text to Column function in 5 simple steps.

5 Steps to spread the data of a cell across columns

Step 1. Select Cell

Select the cell where your data is there to be split across columns

Step 2. Click on Text to Columns Option

Click on Text to Columns Option available under Data Tab as shown in the below picture
 
Text-To-Columns

Step 3. Choose the Type

In this dialog box user can choose the type of data which he/she wants to split. As you can see in the below picture there are two options available.
 
1. Delimiter : Choose this option if your data is joined by a delimiter by which you want to split it.
1. Fixed length : Choose this option if your data does not have a delimiter rather you want to split it by a fixed length. This option will enable user to drag the split bar to adjust it where user wants.
 
Step 1 of 3 - Text To Columns Wizard
From the above screen i have selected first option as delimiter as my data is delimited by comma (,) delimiter.

Step 4. Choose or enter the delimiter

In this wizard user is allowed to select one of the already listed delimiter or you can enter your own delimiter by selecting the Other checkbox as shown in the below picture:
 
Step 2 of 3 - Text To Columns Wizard
You can see your data preview how it is split the data as shown in the above picture.
Note:
1. If you had selected fixed width option in above Step 3 then you will be allowed to drag the column separator to adjust the width of all the columns.
2. In the above wizard you can see one option as Treat consecutive delimiters as one. This option overcomes the problem which occurs in VBA SPLIT() function. In case of consecutive delimiters, SPLIT() function stores an Empty character as a split item. To know more about this you can refer this article. But here in Text to Columns options you can get rid of this problem by checking this checkbox.

Step 5. Last Step : Select the destination

By default destination is selected as the same cell where the data is to be split. This you can choose your own. If you keep the same cell as destination then your original joint data with delimiters will be replaced by the first split. Here I have selected the destination cell as the adjacent column cell in same row. Hence the first split will be put in the destination cell selected and rest all split will be put in to the separate columns as shown in the below picture
 
Note: Here in this wizard you can choose the format of the column by selecting the particular column from the Data preview as shown in the below picture:
 
Step 3 of 3 - Text To Columns Wizard

That’s all. Now you are done. Click on Finish and your data of Cell D4 is spread across columns starting from my selected destination cell D5. Refer the below picture:

Text-To-Columns-Split

Your data is spread across columns as you expected without any VBA code. Isn’t this interesting and very useful function of Microsoft Excel? Do you agree with me? Let me know by commenting here !!

Enjoy your Weekend 🙂

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest