While working with data in Excel, sometimes you need to merge multiple cells into a single cell. But here the problem is when you do that you can only get the value from the upper left cell from all the cells you want to merge into one.
Now, to solve this problem there are a few methods that you can use to merge cells without losing data, which means getting data from multiple cells into one.
Fill Justify to Merge Cells with Losing Data (Getting Values in One Cell from Multiple Cells)
If you want to merge values from multiple cells, you want to do it for once, and these values are in a vertical order then using the fill justify is the best way.
In the above example, we have values in the range A1:A5, and now you need to merge these values in cell A1 only.
- First, you need to make sure that your column needs to be wide enough that when you combine all the five values there should be enough space in cell A1.
- From here, you need to go to the Home Tab.
- Now from the home tab, you need to go to the editing group and then click on the fill dropdown.
- Next, from the fill drop down you need to click on the “Justify” option.
The moment you click the “Justify”, it will merge values from all the cells into a single cell i.e. A1.
As I said earlier, you need to make sure that the width of the column needs to be enough so that it can merge all the values in a single cell. Otherwise, the rest of the values will get split to the second cell.
Formulas to Merge Cells with Losing Data (CONCATENATE and TEXTJOIN)
If you want to merge cells on a real-time basis and don’t want to lose data too. The best way is to use a formula. And in Excel, we have two different functions to create these formulas.
Out of these two, my personal favorite is TEXTJOIN, this is a new function and easy to use as compared with CONCATENATE.
TEXTJOIN has three arguments that you need to define:
- delimiter: You can use a delimiter that you want to add in between values that you want to merge into one cell.
- ignore_empty: A TRUE or FALSE to define if you want to ignore empty cells or not.
- range_array: A range/array from where you want to merge values.
And in the same way, you can use the CONCATENATE to create a formula to merge values into a single cell.
=CONCATENATE(A1,” “,A2,” “,A3,” “,A4,” “,A5)
VBA Code to Merge Values into a Cell without Losing Data
You can use a VBA code to merge multiple cells and not lose data. Below we have a code that you can use.
Sub vba_merge_with_values() Dim val As String Dim rng As Range Set rng = Selection For Each Cell In rng val = val & " " & Cell.Value Next Cell With rng .Merge .Value = Trim(val) .WrapText = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With End Sub
To use this code, you need to select the range that you want to merge and then run this code.
This code takes values from all the cells one by one, then merges all those cells, and then add that combined value in the merged cell as you can see in the above example (There’s one thing that you need to know when running a code, you can’t use undo option).
- Excel Quick Analysis Tool
- Apply Multiple Filters to Columns
- Highlight Top-Bottom N Values in Excel
- Insert Checkbox in Excel
- Multiply in Excel using Paste Special Option
- Add and Use Barcode in Excel
- Default Font in Excel
- Filter by Color in Excel
- Rename a Table in Excel
- Use Column Numbers in Excel
- Grey Out Cells in Excel
- Make Cells Bigger in Excel
- Remove Dashes in Excel
⇠ Back to Advanced Excel Tutorials