Free Microsoft Excel 2013 Quick Reference

How to merge two cells which both contain data?


In collum A there are Names, in colllum B are family names.
I have like 1000 of them, and I want to merge them together, without losing any data. How can I do it?
And, is there any way to change entries (text) which is all typed in CAPS to normal say from JOHN BLACK to John Black ?

Thank you

Post your answer or comment

comments powered by Disqus
Why to Merge Excel cells

While we are working within a Microsoft Excel spreadsheet, many of us may face this problem: we have to merge two or more cells together to form one big cell. Spire.XLS, as a professional and powerful Excel component, it enables developers to merge Excel cells into one with C#/VB.NET effortlessly. The whole process is very simple, we just need choose the cells and everything will be cool with two main sentence of code.

How to Merge Excel Cells via Spire.XLS

Please make sure Spire.XLS and Visual Studio are correctly installed on system. Follow the simple steps below to merge Excel cells with C#/VB.NET.

Step 1 Create Project
Create a C#/VB.NET project in your visual studio. Add Spire.XLS.dll as reference. The default setting of Spire.Xls.dll is placed under "C:Program Filese-iceblueSpire.XlsBin”. Select assembly Spire.Xls.dll and click OK to add it to the project.

using Spire.Xls;

namespace CellsMerge
class Program
static void Main(string[] args)

Step 2 Load Excel File
Use the code below to load Excel file in which we will merge Excel files.
//load the excel template
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"", ExcelVersion.Version97to2003);

Step 3 Merge Excel Files
Select the cells which we need merge into one. Follow the samples below and we can merge cells at will.

//merge A2-D4 to one cell
Worksheet sheet = workbook.Worksheets[0];

//merge D5-E6 to one cell

Step 4 Save and Preview
After finish merging Excel cells, save the modified Excel file as .xls format. And we can preview what we’ve done by pressing F5 to launch the project.

//save the workbook
workbook.SaveToFile(@"....cellsMerge.xls", ExcelVersion.Version97to2003);

//launch the workbook


Below you see a previous thread with a similar problem to mine:

Now, my question is: I have the exact same problem, but how do I change the code when the two numbers I want synced are on differen worksheets (but same workbook).

I appreciate if somebody could help me! Thanks!

" 05-05-2006, 07:22 AM
Registered User Join Date: 05 May 2006
Posts: 11

How to keep two cells in a excel in sync by cross linking

Hi , I want to keep two cells in sync with the same data. The problem with the link option is that we can have one source and any target . But I am interested that same value be shared across two cells so that if one cell updates the value then the other dependent cell automatically updates its data.

Please let me know how can I do this?


If both cells are in the same Worksheet, the following code should be pasted
in worksheet's code (right-click on tab name and select Code)

I used A1 and A2 as example addresses. You can modify these.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell1 As Range
Dim rCell2 As Range
If Target.Count > 1 Then Exit Sub
Set rCell1 = Range("A1")
Set rCell2 = Range("A2")
Application.EnableEvents = False
Select Case Target.Address
Case rCell1.Address
rCell2.Value = rCell1.Value
Case rCell2.Address
rCell1.Value = rCell2.Value
End Select
Application.EnableEvents = True
End Sub

Can anyone help me
I would like to know how to merge two cells of data from one excel work sheet in to one cell on another

any help would much apreciated


I would like to merge two cells without losing the data ea: Column A= AB and
Column B = 1234. End result want to achieve Column C = AB1234 Please help -

how to merge 2 cells and have them on 2 separate lines in that cell, one
above the other.


How to copy the cell which has formula by using macro

* I need to copy the 1500 cells in one column and paste into multiple times [20]

Attached sheet for referance...

1.The sheet is opening with 16 column
2.If i give input as 20, further 4 columns to be added [ Not affecting the column 'U' ]

Please guide me to solve the same.


I have content in two columns side by side. A total of 1650 rows. I am trying to merge the content of both rows using =A1 & " " & B1 however, it is truncating the content I guess because the cells contain too many characters. How do I merge the contents of the two columns successfully?


Is there a way to compare two cells to see how many
numbers are same?

For example, A2 contains 16, 17, 19; B2 contains 16, 17; and I want to
in C2 the result 2. What is the formula for this?


sun1x's Profile:
View this thread:

Is there a way to compare two cells to see how many
numbers are same?

For example, A2 contains 16, 17, 19; B2 contains 16, 17; and I want to put
in C2 the result 2. What is the formula for this?


I had posted earlier that I couldn't sort my file. Tried what advise was offered but it still didn't work. I was searching trying to find out why I couldn't sort and found I had split a column into two columns (cells) and somehow I managed to create two tables instead. Now I can't sort the file using those two cells which are names (first & last). How can I change that one cell so that it is not a table unto it's self but part of the rest of the table? I believe this is why I can't sort the file as I need. I sure hope I'm making sense here, cause I need this file sorted so I can use it Sunday for function where I can check folks to see if they have purchased a ticket. Please help. Oh, I'm using 2007.

Dear friend
Can u guide me how to merge two columns data into one
i have column A ABCD
column B 1234
I want to merge both to show ABCD1234 In one column
can u guide/help me?


Anyone knows how to compare two sheets, say sheet "A" and "B",

deleting data (rows) in sheet B which is not found in sheet A, and

copying data (rows) from sheet A which is not present in sheet B?

Data (rows) found both in sheet A and B should stay untouched.

Best regards


I have two cells which both contain dates
C1 contains todays date (=NOW()). C2 contains a date in the future.
What I would like to do is send a pre-programmed e-mail message using Outlook, to a fixed e-mail address when C1 = C2 without any intervention e.g. having to enter any more details into Outlook.
Can anyone think of another way of alerting people without using e-mail?
Thanks eD

Dear all,

I meet a question about following.
in A column, there are many data(some data are same), and in B column,
there are many relevant data, but they are different, how to merge all
the revelant data to one cell in B column with the same in A column,
thanks in advance!

For example

123 R1
123 R2
123 R3
123 R4
124 L1
124 L2
124 L3
125 C1
125 C2
. .
. .
. .


I have 7 different workbooks that link back to one Master workbook. Each
workbook has a Remarks Cell. I would like to know how to merge each of the
remarks into one Remarks Cell on the Master workbook without over wrting each
EX: Sales Remarks from sales Workbook
ARCH Remarks from sales Workbook
Banking Remarks from sales Workbook
List all in the same cell with marked remarks without over writing on the

Please Help



I'm pretty new to excel and having trouble merging the contents of two
cells. What I want to do is automatically merge two cells into one with a
carriage return (ALT+ENTER) between each cell value. Is this possible? Please

Thank You

Cell A- Hello
Cell B - 02/27/2010
how can I write the following: If (rd1=Hello; than show value cell B- 02/27/2010; else shov value cell B+30days - 03/28/2010)

I don't quite know how to explain


Hey, been a while, busy with work.
I need to know if I can merge two cells data in one piece of information. I am not looking to actually merge two cells, but have the data show in a single cell from two existing cells.

A demo file with the out I am looking for.


Please help. I am running a football pool and cannot for the life of me figure out how to sum highlighted cells which would indicate wins in my pool. I have looked all over the forum but I am not a programmer so I need step by step instructions. I am running Excel 2010 on Windows Vista.

I have tried the following formula but it did not work =Sumcolor(B3:N3,6,FALSE)
I read 6 was the formula number for the color Yellow.

I really need an elementary explanation since I am not a programmer. Thank You in advance for your help.

What: I'm wondering if there is a way to merge two spreadsheets, but keep the data that is new on the newer sheet, and delete all else.

Why: I run a weekly report, and have no way of determining what data is new, and what was in the report last week. I'd like to be able to extract the new data, by laying it over the old data, and having all the matching data delete, leaving only the new data.

If anyone knows of a better, less convoluted way to accomplish the same task, please let me know, and thank you in advance for your help.

I am attempting to sum the number of occurrences for the following:
(1) Column C contains the number 3 (all cells in this column contain both text and numbers)
(2) Column B equals "Open"
(3) Column K equals "Design"

This is my working formula, but isn't working. The error is in the first array, I've forgotten how to find/sum cells which contain certain text/numbers.

--ISNUMBER(MATCH('AIM data'!$C$2:$C$10000,{"*3*"},0)),
--('AIM data'!$B$2:$B$10000="Open"),
--('AIM data'!$K$2:$K$10000="Design"))

TIA -James

Hi everyone,

stupid question I did not find an answer for even after a long search:

I have an Excel table with cells which contain fax numbers. One cell in a row contains the dialling code, the next cell in this row contains the fax number.
For a series-letter I created with Word, these two cells have to be merged into ONE, so Word can read the fax number including the dialling code in "one piece".

So, for example, I merge A1 ("040") with B1 ("636465"), the result should be A1 ("040636465"). But the standard procedure to merge/connect cells in Excel only preserves the content of the first cell, so after merging A1 and B2, the content of B2 is lost.
What a mess.

How do I merge two cells into one, so that the content of these two cells is combined in one cell?

Any ideas and/or solutions greatly appreciated!

Thanks & have a good week


I have a large dataset with many rows and columns. Many individual cells are missing data. For any two specified columns, I would like to count the number of rows that contain data in both of the columns. I assume this is a variation of the COUNTIF function, but I can't figure out how to adapt it. I am trying to calculate degrees of freedom to then calculate the significance of my Pearson correlations. Thank-you in advance for your help.

No luck finding an answer? You could always try Google.