Friday, December 2, 2022

How to Split Cells in Excel? [Split Column into Multiple]

How to split cells in excel? Let’s learn!

Everyone works in MS Excel, at least once in a while. After all, it is a coveted spreadsheet program. From numerical data storage and analysis to creating charts for presentation, Excel offers it all and more to make life easier for the everyday user. Whether you are already working or looking to work in MS Excel, in either case, knowing how to split cells in Excel is important.

The importance of columns and rows cannot be overemphasized. They are the building blocks of any spreadsheet. At times, you need to separate two cells from each other for better readability, easy computing, and data presentation. It can be stressful, especially if you are new to Excel. Breathe easy, here’s how to split a cell in Excel?.

How To Split Cells In Microsoft Excel?

Splitting cells in Excel is easy, provided you follow the following steps to the tee.

  • First, open the spreadsheet and select the particular column which you want to divide. You can select the column by clicking on the heading of the column.
  • Now, click on the Data tab and choose the “Text to Columns” icon from the Data tools of the ribbon on the top.How-To-Split-Column-In-Excel
  • It will show you two options:
  1. Delimited: You can select this one if the data has a particular character by which column can be separated like empty space, dash etc.
  2. Fixed width: You can select this one if you need to split the data at the set numbers of characters.split-the-data
  • In case you select Delimited to choose a character to set as a split, then click on Next.select-Delimited
  • 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.select-Fixed-widthimplement-split
  • At last, choose the data format you need and click on the “Finish” button. Now data of the column will be moved to the new column on the right side.

How To Split One Column Into Multiple Columns In Excel?

Now that you know how to split a cell in Excel, let’s focus on how to separate multiple columns.

From Flash Fill (Applicable in Excel 2013 and 2016)

  • First, go into 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 keys on your keyboard to execute it.
  • It will automatically separate all of the first names from that particular column.

Mind you, before selecting Flash Fill, make sure that you 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 the columns.
  • At last, for extracting the last characters in the data, you can use the right text function then select cell D2 and apply the RIGHT (A2,5) formula.

 Conclusion

How to split the column in Excel – it’s a common question every MS Excel user is asking. Sometimes you need to separate two columns from a single column. We hope that you understood the process, but you can ask any question if you have queries.

People are also reading:

Vinay Khatri
Vinay Khatrihttps://learndunia.com
I am a Full Stack Developer with a Bachelor's Degree in Computer Science, who also loves to write technical articles that can help fellow developers.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles