If you work in Excel, then you know the importance of columns and rows because these are the building blocks of any spreadsheet. Sometimes you need to separate two columns from a single column, but you don’t know how to split column in Excel. So in this article, we will give thorough information and ways to split cells in excel/Split Columns in Excel because it can help you to create more convenient and easy to read spreadsheets.
How To Split Cells In Excel?
- First, open the spreadsheet and select the particular column which you want to divide( You can select the column by clicking at the heading of the column.)
- Now click on the Data tab and choose Text to Columns icon from the Data tools of the ribbon on the top.
- It will show you two options:
- Delimited: You can select this one if the data has a particular character by which column can be separated like empty space, dash etc.
- Fixed width: You can select this one if you need to split the data at the set numbers of characters.
- In case you select Delimited to choose a character which you need to set as a split then click on Next.
- In case you select Fixed width, go in the option and click on the point where you need to implement split then click on Next.
- At last, choose the data format you need and click on finish. Now data of the column will be moved to the new column at the right side.
- How to Insert PDF into Excel?
- How to Merger Cells in Excel?
- How to Upgrade Windows 10 Home to Pro?
- How to Create a Bar Graph in Excel?
How To Split One Column Into Multiple Columns In Excel?
From Flash Fill (applicable in Excel 2013 and 2016)
- First, go in the spreadsheet and select the column then in cell B2, you need to type the name as shown in the image.
- Now go to the Data tab then click on the Flash Fill or you can press Ctrl and E key on your keyboard to execute it.
- It will automatically separate all of the first names from that particular column.
- (Before selecting Flash Fill, make sure that you have to select cell B2 otherwise it will give you a warning pop up on your screen.)
- From LEFT, MID and RIGHT Text String Functions (applicable in Excel 2010, 2013 and 2016)
LEFT function: It returns the first character or left side characters according to the specific number of characters you need.
MID Function: It returns the middle number of characters by string text beginning at the place you specify.
RIGHT Function: It provides the last character or characters through the text field that depends on the particular number of characters on the right side.
- For extracting the address through the LEFT function, you need to select cell B2 then apply the LEFT(A2,4) formula.
- For extracting the City data, you can use the MID function:
- You need to select cell C2 and apply the MID(A2,5,2) formula.
- Now right click then drag the cell for copying the formula in the rest of columns.
- At last, for extracting the last characters in the data, you can use the right text function then select cell D2 and apply RIGHT(A2,5) formula.
In this article, we have offered complete information on How to split the column in Excel. So as we have previously discussed, if you work in Excel, then you know the importance of columns and rows because these are the building blocks of any spreadsheet. Sometimes you need to separate two columns from a single column, but you don’t know how to split cells in Excel or Split a Cell in Excel. We hope that you understood the process, but you can ask any question if you have queries.
You may be also interested in: