Merging and splitting cells in Microsoft Excel is very similar. Below are the steps required for merging and splitting cells in each of these programs. These steps help you with How to Split Cells in Excel.
Follow the step by step process and split excel cells into multiple cells. This tutorial will help you to split cells in Excel.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
How to Merge Cells in Excel
Note: This document only covers how to merge cells in excel, not how to merge the value of more than one cell.
In Excel, you can easily merge cells with 3 easy steps,
- Step 1: Open Excel
- Step 2: Highlight two or more adjacent cells you want to merge. For example, highlight cells A1 and B1 or C1 and C2.
- Step 3: Now, Click the Merge Cells button in the formatting bar.
In Microsoft Excel 2010 and above, the Merged cell feature can be found under the Home tab -> Merge & Center.
Note: When cells with multiple values are merged, the upper-left most cell will be the data of the merged cell.
Merge two cells using a formula in Excel
In Excel, you can merge two or more cells using a formula. Using the ampersand (&) in a formula acts to combine two or more cells or a cell and other text together.
In this example, the formula =A1&B1 combines the values in cells A1 and B1 into cell C1. In this same example, if you wanted space between the two cell values, the formula would include a “space” in it and be written as =A1&” “&B1. You would use double quotes around a space to add a space in the formula and the resulting merged value.
How to Split Cells in Excel?
Here we discuss the two different methods to split cells in excel.
1. How to Split Cells by Character in Excel?
To Quickly Split Cells by Character in Excel, you have to follow below steps,
- Step 1: Open Calc or Excel.
- Step 2: Highlight the merged cell you want to split cells by character.
- Step 3: A merged cell in Excel will expand into multiple columns or rows.
- Step 4: Click the Merge cells by Character button in the formatting bar.
Below is an example of what this button looks like in each of the versions of Excel and Calc that support this option.
Unfortunately, splitting a cell in Excel 2000 is not as easy as clicking the merge button as explained above (this was introduced in later versions of Excel). To split a cell in Excel 2000, follow the steps below.
- Step 1: Highlight the merged cell you want to split. A merged cell will expand into multiple columns or rows.
- Step 2: Click the Format drop-down menu.
- Step 3: Click the Alignment tab.
- Step 4: Within the Alignment tab, uncheck the Merge cells checkbox.
2. Split Unmerged Cell Using a Formula
In Excel, you can also split an unmerged cell using the Text to Columns option.
- Step 1: Select the cells you want to split into two cells.
- Step 2: On the Data tab, click the Text to Columns option.
- Step 3: In the Convert Text to Columns Wizard, if you want to split the text into the cells based on a comma, space, or other characters, select the Delimited option. If you want to split the text based on a specific number of character length, select the Fixed width option. Then click the Next button.
- Step 4: For the Delimited option, select the character you want to split on. For the Fixed width option, select where you want to split the text by clicking in the Data preview section of the Wizard window. Then click the Next button.
- Step 5: In the last step, select any specific text formatting options for the split text. Then click the Finish button.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12