Excel tutorials

Article Index

Search and Find the Merged Cells

Merging cells in Excel

About Cells and acts on them (selecting, copying, moving, etc.) I wrote already on this website - tutorials (see the content of the topics on this site). Excel has the ability to Merge Cells. If you ask a question of how multiple cells to create a single cell then refer to the following instructions. To merge two or more cells into one cell (one unit) is sufficient to select a range of cells that you want to connect and click in the group commands Alignment => Merge & Center command / button. In the picture below you see a merged cell (gray cells).

You notice that the following cells merged into one (merged cells retain the address of the first cell in the upper left corner)

B2=> B2:B3, B5=> B5:B7, B10=> B10:B13, B15=> B15:B16, D3=> D3:E7, D10=> D10:E10, E13=> E13:F14.

How to merge multiple cells into one cell in Excel

How to find the merged Excel cells

If you are working on the worksheet for the first time and you are not familiar with the organization of data and you want to find out that all the cells are merged in a worksheet, or you want to know the address merged cell do the following:


On the ribbon, in the Home tab Editing group commands, click Find & Select => Find

He opened a dialogue window Find and Replace, click the Format button and select the Format command.

How to find the merged cells in Excel using Find and Replace 

In the next step, you open a new dialog window 'Find Format'. In this dialog box, turn ON (check box) in front of the 'Merge Cells' and then click on the OK button

How to turn ON the option to Merge Cells Excel finds only the merged 

Search Merged Cell step by step

Again you are return to the dialogue box 'Find and Replace'. Now in this dialog box, click on the 'Find Next' button. Excel will select the first cell merged, after the next click on 'Find Next' button Excel will find the next merged cell and so again in a circle (loop). (You note the picture below to Excel frame the merged cells green frame). If you look at the address field, you will see the address of the first cell of all merged). 

How to search and find next the merged cells, step by step - cell by cell 

How to Quickly Select a Specific Address a Merged Cell in Excel

In case you have a lot of data in the worksheet and in addition worksheet contains dozens of merged cell, the question is how to quickly position on a specific cell merged. The solution lies in the 'Find All' button. Clicking this button in the dialog box 'Find and Replace' will appear a list of all the cell references that are first in merged cells. Clicking on any of the hyperlinks Excel you automatically positioned to address respective merged cell

How to quickly position on a particular merged cells in Excel 

You notice in the picture above list on the Worksheet (Sheet) and in the picture below you will see a list inside the Workbook. This list is determined by the choices in the 'Within', in which the drop down menu select Sheet or Workbook. 

 How to create a list of all the hyperlinks on the merged cells in Excel workbook

Find all merged cells and highlight it by color

If you want to find all the merged cells in the worksheet and mark them color then use the VBA macro below. 

Use VBA to find and coloring all the merged cells 

VBA macro for a solution in the image above is as follows. This VBA macro to copy the 'Sheet Module'. Save the workbook as *.xlsm format to support execution of VBA.

Sub FindMergedCellsAndColorIt()
'finds all cells that are merged and paint them the color
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
c.Interior.ColorIndex = 3 'number 3 for red color
End If
End Sub

How to Numbering Merged Cells in Excel

If you want to number the merged cells in the row or column, then use this VBA macro below.

 How to numbering merged cells in Excel

If you want numbering merged cells in Columns replace (change) the piece of code that refers to the OFFSET command

'Option Explicit
Sub NumberingMergedCellsInRow()
'numbering merged cells in Columns
Dim b As Integer
Dim c As Integer
Dim TheEnd As String
b = 0
TheEnd = InputBox("Enter up to any number you want to make a numbering:" & Chr(13) & "(must be entered a whole number)", "Numbering")
If TheEnd = 0 Or TheEnd = "" Then: Exit Sub
Range("A1").Offset(b, 0).Select 'For the numbering of the Columns of cells merged replacing Offset (b, 0) in the offset (0, b)
a = Selection.Cells.Count
If a = 1 Then
b = b + 1
ElseIf a > 1 Then
b = b + a
End If
c = c + 1
Range("A1").Offset(b, 0) = c 'For the numbering of the Columns of cells merged replacing Offset (b, 0) in the offset (0, b)
If c = TheEnd Then: Exit Do
End Sub

Merge Excel Cells Without Loss Data

If you need sometimes merge multiple cells into one and the respective cells contain specific information. Of course, you do not want to lose the respective data but you want data from multiple cells are merged in a one cell. The solution to this merger cells with no lost data see in the picture below. 

 Merge Excel cells without data loss

Take the example in the picture above. You note in column 'A' text data (name company employees). You want all the names merge into one cell. Given that, 'names' at the end of words have no sign that we used for the 'Find and Replace' that you may add a 'comma' and ultimately had separate words, take advantage'll formula in column 'B' which reads =A2&", " or =CONCATENATE(A2;", ") and copy down.

Copy the cell in column 'C' in the column 'D' on the way to paste copied in column 'D' as the Paste Special => Value.

Spread the column 'D' on wide enough to fit all the words (names) after the merger.

Select the cell in column 'D'.

Then click the ribbon tab Home => Fill => Justify

Now at the end you have all the words (names) in a single cell, if you think that the cells are too wide, format it as 'Wrap Text'. (VBA source unknown, I do not remember, just Internet Networks)