We use Excel sheets for different kinds of projects. If you want to merge cells in excel or combining text from one or more cells is vital when we are editing it. Sometimes we may lose data in this process. Here we guide you with simple steps on merging data cells without losing any data. Let us get started.
Merge Two Cells In Excel Without Losing Data
- To merge the cells, use the «Alignment» tool, which can be found on the main tab. Select the cells that need to be merged. Click «Merge and Center». When the two cells are merged, only the data contained in the top left one is retained.
- How to combine multiple cells in excel without losing data? Easiest way to do this is by the use of ampersand, ie. =A&B If Cell 1=A and Cell 2=B, then the contents of both cells can be merged within Cell 3 by applying formula =Cell1&Cell2. Please refer to the following excel spreadsheet for example.
- Feb 24, 2021 To answer your question directly, Select cell G4 to Jdata. Go to the Home tab, and click the arrow next to Merge, an you should see other options. Choose Merge Across, and it will do all the cells that you have selected row by row. Also, for future reference, I suggest not posting personal information in your posts.
How to merge cells into a single cell without losing data Select a range of adjacent cells to merge Click the Combine Data button on XLTools tab. Select Into a single cell. Set a suitable separator.
How to Combine Text from Multiple Cells into One Cell?
Text from multiple cells can be combined into one cell by using the ampersand (&) operator.
To combine the text in two cells A1 and B1, steps to be followed are as follows:
- We have to select the cell in which we want to place the combined data.
- To start the formula, we have to type an equal sign =
- Then we have to click on the first cell, i.e., A1, then type the ampersand operator (&)
- If space is needed between the text of multiple cells, type the text string “(double quote, space, double quote).
- Now click the second cell, i.e., B1 and press enter to complete the formula.
How to Merge Cells in Excel?
To merge the cells in excel, Merge and Center option can be used in the home tab. But using this option, cells can be combined but not the text.
When we select the cells and use the “Merge and Center” option, it will keep the writing from the leftmost cell and remove the text from all other cells.
To merge the cells without losing text, we can use the “CONCATENATE” function. To merge cells without losing text, we can use the “Center across selection” option as an alternative to joining and Center option.
How to combine cells in Excel?
To combine the cells in excel, we have various options.
- We can use merge and center option
- We can use the ampersand (&) operator.
- We can use the CONCATENATE function.
Excel concatenates multiple cells.
Concatenate function helps us to combine the content from multiple cells into one destination cell without retyping all the text. Steps to be followed to concatenate are:
Initially, we have to select the cell in which combined text to be placed. Then type the function into the cell, which starts with an equal sign (=) followed by concatenating.
Arguments can be entered in two ways,
First, we have to open the parenthesis, type the cell references (A2, B2) separated by commas and then close the brackets at the end.
=CONCATENATE (A1, B1). Otherwise, we can click on the A2 cell, type a comma, then click on the B2 cell and then type the closing parenthesis.
If you see an alert message, click on yes.
Now we have to press ENTER after adding the cell references to the concatenate function.
No space is found between the combined texts as the concatenate function combines precisely what is in the arguments.
You can also use =A1&B1 to merge shells data
=CONCATENATE (A5, “”, B5)
To fill sequential data into Excel with the fill handle, we need not type the function in every cell in the column. We can quickly copy the function to the other cell.
Merge Two Cells In Excel Without Losing Data
Excel merge cells vertically without losing data
The quick and simple way to combine two or more cells vertically is to use the built-in merge option combined with center option.
- First select contiguous cells you want combine
- Select the alignment group from the home tab.
- Now click on the merge and Centre button.
- When we click the button, the cells start to combine into a single text
- There are other options called Merge across Merge cells that work similarly.
Align the text after merging.
- First, select the text you wish to align with.
- Now select the desired alignment from the alignment group.
- This is found on the home tab.
Also Read: How to Merge Excel Files and Sheets
Why can’t I merge cells in excel
Several reasons make cells unable to be merged
The most common reasons are:
- The cell is in a table
- The cell is in a protected sheet
When you are cell is in a table, you have to merge it before creating the table.
When your cell is in a protected cell editing is denied
In such cases, we need first to unlock the sheet and then go for the merging option.
There are many other problems that will not allow merging the cells.
- When the cells have data in the form of image
- When the entered data is massive, merging and aligning becomes a significant issue.
Merge data in excel
Combining the cells using & symbol
- First, you need to select the cell in which you want to put the combined data
- Now type = and select the first cell that you wish to combine.
- Now & with the help of quotation marks enclosing space
- Now select the next cell you wish to combine and click enter.
Combining using the concatenate function
- Here you have to select a cell where you wish to put the combined data.
- Now type = CONTACT(
- Now you need to select the second cell which you wish to combine by using quotation marks, spaces, commas, or other text.
There are some problems in which merging becomes an issue.
We need to take care of the problems accordingly and later merge them.
You can merge cells horizontally and vertically into a single cell.
Aligning is the crucial point here, and do not forget to get the data to the center.
Since you’re here, you’ve must tried to merge and center two cells to get them combined, but to your surprise, you only got the data in the left cell and right cell data is gone. Even it displayed a warning before merging them, right? To combine two cells we use merge and center but it is used for formatting purposes, hence you only get data in left-upper cell. Its can’t be used to combine data.
Ok. Then question arises how do we combine two columns in excel without loosing any data. It can be done. Let’s just see how.
How to Combine Two Columns in Excel Using Formulas
We all know about the CONCATENATE function of excel. It is used to concatenate two or more strings together. These strings can be in cells. So let’s see an example.
Combine Two Columns In Excel Excel
For this example, we have this sample data. See below image. We need to combine column A and Column B into one to get full name.
To combine First Name and Last Name we will use a helping column. Lets name it Full Name in column C.
Now in C2 write this CONCATENATE formula and drag it down.
CONCATENATE Function in excel combines given arguments into one. Here we are adding first name and last name with an space (“ “) in between them.
Now if you don’t want first name and last name column in sheet then first value paste Full Name Column.
- Select all cell in C column. You can use excel shortcut CTRL+SHIFT+down arrow, if you are in cell C2.
- Copy it using CTRL+C
- Now right click on cell C2 and click on Paste Special or press ALT>E>S>V sequentially.
- Select value and OK
- Select Column A and B and delete them.
And its done.
So yeah its done. You have merged two columns without loosing any data, successfully.