Do you need to organizing your data in multiple columns instead of keeping the string in a single column for further data process? This can be helpful when trying to find specific information or when trying to sort data. Additionally, columns can be used to create formulas. Formulas are a great way to analyze data and can be used to create charts and graphs.
In this post you learn how to use Text to Columns and Text functions ( LEFT and RIGHT ) to split cells into columns in excel.
How to use Text to Columns feature to split cells into columns
Suppose you have received a data set like below, It has concatenated of three fields Name, Age, and Country. You create a report that displays the data in a sorting order by country.
Follow the below steps to use Text to Columns feature to split cells into columns in excel
Select your data
Go to Data menu
Press Text to Columns
Choose the Delimited option button, since your data has comma delimiter
Press Next
Choose Comma
Press Finish
You can see your data splitted in a separate column Name, Age and Country. Now you can apply sort for the Country column.
How to use Text Functions Left/Right to split cells into columns
In this section you learn how to use the text functions and search function to split cells into column
Suppose you receive a csv file which has two field name and age. Please follow below steps to split the text name and age using the text functions.
Type the formula =LEFT(C3,SEARCH(",",C3)-1) in cell D3
Type the foluma =RIGHT(C3,LEN(C3)-SEARCH(",",C3)) in cell E3
Select both the cells D3 and E3
Drag to fill the formula to cells D4 to E9
Conclusions
You learned two simple methods to split cells in excel. Most of the time you receive a csv file and need to split the data by delimiter. If you know any one of these methods to split cells into columns you can simply read and process the data file in excel.
If you have any questions, please leave a comment below and I will get back to you as soon as possible.
