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. Here’s how to merge cells in Excel and how to split cells in Excel.
In case you have a requirement on combining multiple columns to on column & you did not have a clue then this whole article is for you. In this article we are going to learn how to combine multiple columns to one column using vba code. From below snapshot:- Following is the snapshot of require output:-We need to follow the below steps.
Select all the columns that you wish to combine together. Press 'Ctrl + C keys' to copy. You can also copy from the home tab. With this, you will copy the columns and list them in the clipboard as once. In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet). Example: I have a sheet containing rows of names which contain data for up to three choices made, along with any options for that choice in the next respective column.
You have a table in Excel and what you want is to combine two columns, row-by-row, into one. For example, you want to merge the First Name & Last Name columns into one, or join several columns such as Street, City, Zip, State into a single 'Address' column, separating the values with a comma so that you can print the addresses on envelops later. Splitting a very long column into multiple cells can make the difference between an easy-to-read Microsoft Excel document and one with data that is poorly structured. In the latest versions of the software, you can split the data in a long column using a feature available in the 'Data' menu.
Every now and then in Excel, you'll need to merge two or more cells in a range. One example may be in the case where a dataset is spread across several columns, and for the sake of presentation, you may want to merge the cells containing the heading or title of that dataset to make them behave like one cell.
At other times, ‘merging a cell’ may mean extracting and actually combining data that are located across several rows or columns and placing them in another row or column.
Let’s talk about how to do both.
Download your free merge/split practice file!
Use this free Excel merge/split file to practice along with the tutorial.
A quick and common way to merge cells is to use the Merge & Center command in the Home tab.
Simply highlight all the cells to be merged, then click the Merge & Center icon.
In the example above, cells A1 and B1 were highlighted then the Merge & Center command was selected. The result is that both cells now behave like a single cell, which is given center alignment. The cell alignment may be changed if desired.
If you want to merge cells in different columns in a single row, the Center Across Selection offers a good solution. The command is not as easy to find in Excel as the Merge & Center option, which is a pity, but it does allow sorting and highlighting without any issues.
To use Center Across Selection:
Using this method will display the text as though it has been merged and centered, but each cell can still be selected one by one, separate from each other. Cells can also be sorted and highlighted as normal.
If data is in any cell other than the upper leftmost cell, there will be no loss of data. Instead, cells will appear as normal, as though they have not been merged.
There are other times when “merging cells” refers to combining the actual data that is in multiple cells into one cell.
This can be accomplished through concatenation.
Three simple methods to concatenate or join values in Excel are shown below. They are:
Each of these methods is designed to join two or more text strings into one string.
Using the ampersand (&) symbol between values will join them in a string.
For example, in the dataset below, let’s say we want to have the full name of each individual shown in a single column, column C. Using the & symbol as a concatenation operator is a popular choice because knowledge of function formats is not required although this method is, technically speaking, a formula.
As with all Excel formulas, we would begin with an equal sign. Next, separate each value with the & symbol. Since a space is also likely desired between the first and last names, we would also enter a space within double-quotes.
The CONCAT function allows the selection of a range and is therefore potentially quicker than using the & symbol, especially when no additional characters are required between the cell values being joined.
This would work well in the case of the dataset below:
The syntax of the CONCAT function is:
We can merge the contents of cells A2 to C2 in cell D2 with the entry:
If additional characters are required between cell values, these would be entered within double quotes and separated by commas as is usually the case with function arguments.
TEXTJOIN can be considered an improvement in the efficiency of CONCAT since it allows repetitive delimiters (characters between values) to be entered once.
The syntax of TEXTJOIN is:
The ignore_empty argument is required and is a setting that tells Excel what to do if empty cells occur within the range. If set to TRUE, empty cells are ignored. If set to FALSE, the delimiter is returned nonetheless, resulting in consecutive delimiters with no values in-between.
We can insert dashes between each cell value with the entry:
Click here to learn more about using formulas to merge cells in Excel.
For those times when you’d like to split a single cell with data into two or more columns, the Text to Columns command may be just the thing you need.
For example, let’s say we have the following name list, and we want the names to be split across two columns.
We would do the following:
The result is that the text in column A has been split across columns B and C, using the spaces as delimiters.
With Flash Fill, you can teach Excel what you want your data to look like by entering the first two or three rows with the data in the desired format.
Next, click on the last value you entered, then click the Flash Fill icon from the Data tab in the Data Tools command group.
Repeat for each column, and voila! Your one-column data has been split across two columns.
Finally, you can also use the LEFT, MID, and RIGHT formulas to break up the values in cells according to their position within the string.
For example, let us assume that we have the following dataset containing telephone numbers, which we would like to split into three separate columns as follows:
The syntax of the LEFT function is:
Num_chars is the number of characters in text to return, starting with the leftmost character. If omitted, only the leftmost character is returned.
This formula asks Excel to extract the three leftmost characters from the string in cell A2.
The syntax of the MID function is:
Start_num is the position number of the first character to be returned, counting from the leftmost character in text.
Num_chars is the number of characters in text to return, starting with the leftmost character.
This formula asks Excel to extract three characters from the string in cell A2, starting with the fourth character from the left.
The syntax of the RIGHT function is:
Num_chars is the number of characters in text to return, starting with the rightmost character. If omitted, only the rightmost character is returned.
This formula asks Excel to extract the four rightmost characters from the string in cell A2.
Now we have successfully split the text in one cell into three cells using a formula.
With all these different ways to merge and split cells in Excel, including the data in those cells, you’re bound to find one that suits your needs. Have you found any other methods useful? Let us know in the comments below.
You can also check out our course library to learn some other useful techniques in Excel. You can start with our free Excel in an Hour course to cover some basics. Then upgrade your Excel skills with our comprehensive Basic to Advanced Excel course.
Learn Excel for free
Start learning formulas, functions, and time-saving hacks today with this free course!Start free course