Split Data

Hello friends,

In any organization, the data is stored in Databases, like Oracle, SQL, SQLite, MySQL, Sybase etc., and we collect or download these data from e-commerce software or ERPs like SAP, CRM, ERP5, ERP Next etc., or directly from the database, sometimes you might have noticed that these files are with “ *.CSV ” extension or other extensions which gives us all data in a single row in one Cell! 

But to analyze these data we need to separate these data from one Excel cell to more than one cell in the next columns.
We can split these data using Excel’s Text to Column option. Today we will discuss about Text to column in Excel.

Let us have a look at the below image, which is combination of numbers, texts, special characters and dates.

Excel Split Cells by Text to Column Option

To analyze such complex data, we first need to spread it over the next columns by the data type, like numbers in a column then texts in the next column and so on.

How to split one column into multiple columns in excel?

Let me show you step by step and with related images.

Steps :-

1.     Select the cells row wise

2.     Click on the Data tab

3.     In the Data Tools group, click on the Button ‘Text to Columns’

Text to Columns Option in the Ribbon in Excel

4.     A new window will appear, ‘Convert Text to Columns Wizard – Step 1 of 3’, as shown in the below image

Excel Split Cells by Text to Column Option

5.     Here we get two Options, Delimited & Fixed Width.

a.     Delimited : If we select this option Excel will offer us to split the cell by ‘specified’ criteria, like comma, semicolon, space, tab etc., in the next window. In  this window if you have some special delimiter (e.g., @, !, #, $, * etc.,) besides these in built option (Tab, Semicolon, Comma & Space) you can use the Other option and put your special delimiter to split the cell by this delimiter.

b.     Fixed Width : If we select this option, Excel will allow us to split the cell by fixed width, i.e., character count will be fixed for all cells in that column.

6.     Select your appropriate option and click Next.

Excel Split Cells by Text to Column Option

7.     If you are not using the Fixed Width method, just click Next and  move the upward pointing arrow from left to right as per requirement and click Finish.

Excel Split Cells by Text to Column Option

Excel Split Cells by Text to Column Option
8.     And you are done.

Excel Split Cells by Text to Column Option

Delimited or Fixed Width – Which one to use?

Remember Fixed Width will split cell contents by fixed character count, Let suppose we have got such a data where few digits (uniform number of digits) are at the beginning and then few texts are at the end of the data and we need to separate the numbers and the texts from this data. Here we will use Fixed Width option.

In the below picture, we have a fixed text in the C column as 
“Today is :”, suppose we need to separate or split these two data types One column containing the text part and the next will contain the Data portion.

Excel Split Cells by Text to Column Option

Here we will use Fixed Width option and the final data will be as shown in the below image.

Excel Split Cells by Text to Column Option

On the other hand, if a data is separated by Tab, Semicolon, Comma or Space we will use Delimited option.

I hope you have enjoyed this article, in my next article I will show you few techniques to split the cell using formula and other options available in the Excel.

Thank you for reading…