The tutorial demonstrates different techniques to quickly merge two cells in Excel and combine multiple cells without losing data in Excel 2016, 2013, 2010 and lower. Continue reading
Comments on: How to merge cells in Excel without losing data
by Svetlana Cheusheva, updated on
Table of contents
Comments page 2. Total comments: 42
Hello There,
I have to merge the cells again & again for my business packing list but i don`t know what is the short key of merging cells or if there is no such key then how to set a formula which would merge alternate cells by applying one short key instead of clicking the MERGE & CENTER button with mouse. i have tried aforementioned formula but it is not working after once. can someone help that how it would work continuous?
Stay happy.
Hello! ma'm!
i am facing the problem of splitting the data of such type
100.008400,0.125700
99.983900,0.130800
99.910600,0.146100
99.788500,0.171500
99.617700,0.207000
99.398400,0.252400
into two different columns without commas. Is this possible? If yes than how?
Very easy w/o using VBA
Go to Data Tab, then Text to Columns
Keep it on Delimited (click next)
Unclick the default and click the Comma (click next)
This page will show you how it will look (see Data preview). If it looks good then click Finish. If you click "next" you can format the cells if need be (such as number, text, etc).
How to merge cells with two different cells with some cells containing the first, last name and the other cell containing first, last middle initial?
Exp.
Susan, Smith
Susan, Smith A.
Thanks in advance
Hello Susan,
You can take the following macro as a template and modify, if necessary.
Sub MergeSimilarNames()
Dim sel As Range, sameCells As Range
Dim rowsCount As Integer
Dim priorRowIndex As Integer
Set sel = Application.Selection
rowsCount = sel.Rows.Count
priorRowIndex = 1
Application.DisplayAlerts = False
Set sameCells = sel.Cells(1, 1)
For i = 1 To rowsCount
Dim pos1, pos2 As Integer
Dim SimilarNames As Boolean
pos1 = InStr(sel.Cells(i, 1).Value, sel.Cells(priorRowIndex, 1).Value)
pos2 = InStr(sel.Cells(priorRowIndex, 1).Value, sel.Cells(i, 1).Value)
SimilarNames = (pos1 > 0) Or (pos2 > 0)
If Not SimilarNames Then
If (sameCells.Rows.Count > 1) Then
sameCells.Merge
End If
Set sameCells = sel.Cells(i, 1)
Else
Set sameCells = Application.Union(sameCells, sel.Cells(i, 1))
End If
priorRowIndex = i
Next
If (sameCells.Rows.Count > 1) Then
sameCells.Merge
End If
Application.DisplayAlerts = True
End Sub
Hi,
I have a one of the columns with sequentially repeated values. I am looking for a shortcut or the easiest way to merge the cells with same values with in the column without impacting any other columns/Rows.
Please let me know if you have any simple ways.
Cheers
Hello Sri,
You can use the macro below to merge values in the first column of the selected range. But please create a backup copy of the book.
Sub MergeSameCells()
Dim sel As Range, sameCells As Range
Dim rowsCount As Integer
Dim priorRowIndex As Integer
Set sel = Application.Selection
rowsCount = sel.Rows.Count
priorRowIndex = 1
Application.DisplayAlerts = False
Set sameCells = sel.Cells(1, 1)
For i = 1 To rowsCount
If sel.Cells(i, 1).Value sel.Cells(priorRowIndex, 1).Value Then
If (sameCells.Rows.Count > 1) Then
sameCells.Merge
End If
Set sameCells = sel.Cells(i, 1)
Else
Set sameCells = Application.Union(sameCells, sel.Cells(i, 1))
End If
priorRowIndex = i
Next
If (sameCells.Rows.Count > 1) Then
sameCells.Merge
End If
Application.DisplayAlerts = True
End Sub
Also, you can use our Duplicate Remover add-in to select duplicates. Then you can delete them. In this case cells will not merged.
https://www.ablebits.com/excel-suite/find-remove-duplicates.php
Thank you. Useful
Requesting to present VBA Looping