How to split text into different cells in Microsoft Excel

 If you get some raw data in a Notepad, let's say student data, and you want to move this data into a spreadsheet so you can organize the data for better clarity, then all the data will be pasted into the column that you select. You'll have name, age and score in the same column. If you want to put name, age and score in three different columns, you can use a feature in Microsoft Excel called 'Text to Column'.

Copy the raw data from the source.

Notice below that when you paste it into Microsoft Excel, the data gets pasted into column A. What we are going to learn here is how to split this data into different columns.

Click on "Data" :

Click on "Text to Columns".

You can chose any of the below options, "Delimited" or "Fixed Width". Select "Delimited" for now. We'll look into "Fixed Width" later on.

Click "Next" :

You'll see a window like the one below. The "Tab" option will be checked already, if not, check it. Since our sample data is separated by a space, we can check the "Space" option. Since raw data can have any separator, there is an option called "Other" and a text box next to it. You can fill the text box with any value and the data will be split where ever that value is present.

Click "Next".

Click "Finish".

Now you can see how the data has been added to multiple cells from column A. Without this feature, you would've had to copy and paste the value into each cell which will take a lot of time and is prone to errors.

The above option was for splitting data at a character location. If you want to split data from where you desire, then we can use the "Fixed Width" option. For that, click on "Text to Columns".

Select "Fixed Width" and click "Next".

Move the arrows (circled in red) to the point at which you want to split the data.
Click "Next".

Click "Finish".

Notice below that the data has been split at the locations where you placed the arrow. This will be helpful if there are no separators for your data and you want to split it into different cells.

This option will help you save a lot of time and is less prone to errors. Remember that this will be extremely useful when you are dealing with a huge amount of data.

Don't forget to post your comments and suggestion below.

1 comment:

  1. Very nice information. I think it is a great thing for the people those are unknown about how to split text in different cells of Microsoft excel.