How To Split Cells In Excel?
You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data. For example, you can distribute the first, middle, and last names from a single cell into three separate columns. The key to distributing name components with text functions is the position of each character within a text string. Splitting text from one cell into several cells is the task all Excel users are dealing with once in a while. Today, we are going to take an in-depth look at how you can break strings into different elements using formulas and the Split Text feature. Split text strings into multiple rows/columns by space/comma/delimiter with Kutools for Excel 1. Select the range you will split text strings (in our case we select the range A1:A9 ), and click Kutools Text. In the opening Split Cells dialog box, please check the Split to Rows option. To split a cell in Excel, add a new column, change the column widths and merge cells. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas. Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable. Excel has several features which give us the ability to merge and split cells in multiple ways.
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.
See more:
How To Split One Column Into Multiple Columns In Excel?
From Flash Fill (applicable in Excel 2013 and 2016)
Excel Split Cells Mac
- 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.
Excel Split Cells Value
Conclusion
Excel Split Cells Into Columns
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.
Excel Split Cells Comma
You may be also interested in: