This may not be applicable to all the data sets but may be helpful for a few such as merging two columns that contain names or numbers. Just like the one you see in the figure below – there are two columns containing the names, i.e. first name and last name.
| First Name | Last Name |
| Christopher | Nolan |
| Abdul | Aziz |
| Vikram | Bhatt |
| Walter | White |
Now what we want is that the columns to be merged and the names to be present in one column. If you select the two columns and try merging you find one data set completely deleted. So how do we make the data of both columns remain in a single column? Here’s how:
- Select the third column right after the first cell type =CONCATENATE(A2,B2) (assuming your data is on column A and Cell 2, Column B Cell 2) and hit enter
2) You find the cell with both the data merged into one, all you need to do is select the cell and apply the formula to rest of the column
3) Now if you observe the third column you find names with no spaces in between first name and last name. So here’s a trick on how to get those spaces. All you need to do is create a column right between A and B and insert space in each cell(add space in one cell and apply it to the rest of the column).
4) Now apply the same formula =CONCATENATE(A2,B2) but this time you add three columns to the formula i.e =CONCATENATE(A2,B2,C2)
5) Hit ENTER and you find the names with spaces in between them. Just select and apply the formula to the entire column.
What if the cells contain numbers?
In case the cells contains numbers and letters, you can apply the same formula to achieve the results.
For any reason the output shows an error, just select the column > Copy > While Pasting paste them as values. Do the same to the other column and apply =CONCATENATE
This particular trick is helpful when you want to summarize the reports or data you get from online forms, analytics, etc.
Do you know any other way to make this happen? If yes then please share in the comments.
