How to Merge Three Cells in Excel 2003 without losing the data

Posted on 2012-03-23
Medium Priority
Last Modified: 2012-04-01
Dear Microsoft Office Experts

Note: OS is Windows 7

History:  I copied my SMS messages from Nokia 6300 to my PC using Nokia suite in .csv format because my Nokia phone is almost broken and I was lucky to access to copy messages somehow after too many attempts. Excel sheet got 3 columns & 157 rows . Column 1 got date, Column 2 got time , column 3 got my very lengthy messages. Due to this I can't copy directly to word document because it copied in very weird way only part of the message text is showing up.
So First I want to merge these 3 columns and able to copy in word doco easily to print off as I like to.
Example :
13-Jan-12  7:57:00 PM   SMS Text...........................................
14-Jan-12  2:00:00 PM   SMS Text ...........................................

Please guide.
Question by:MSGK161091
  • 3
  • 2
LVL 42

Assisted Solution

dlmille earned 1000 total points
ID: 37760120
put this formula in column 4 (column D), say, starting row 2:

[D2]=A2 & " " & B2 & " " & C2

Copy that down.

Now, you can copy column D into your word document.



Author Comment

ID: 37760187
Hi Dlmille
It copied A2, B2 C2 to D2  But it messed up the format  14-jan-11 (cell A2) appearing like 40557 in Cell D2.
So time not copied exactly.
LVL 42

Accepted Solution

dlmille earned 1000 total points
ID: 37760195
Ok - let's do this:

assuming A has date, B has time and C a text stream:

[D2]=Text(A2,"DD-MMM-YY") & " " & Text(B2,"H:MM:SS AM/PM") & " " & C2

And copy down

If you still have problems (if not exactly what you want) then upload a couple rows and I can perfect.


Author Comment

ID: 37760552
it worked this  time.. Gr8.... nicely copied to word doco.  I am done with what I want to do.
Thanks a lot lot..

But I want to keep this question open for few days to see how can I able to merge cells without losing the data because when I select 3 cells A1, B1, C 1 and right click to check merge option, it keep the data of cell A1 only after merging three cells into 1.

LVL 42

Expert Comment

ID: 37761522
It will work EVERY TIME, lol.

Feel free to wait a few days, though if we all did that, you'd still be waiting on your answer, lol (just joking ;)

You cannot use the merge function to merge multiple cells together and retain the data.  The concatenation formula is the appropriate approach to achieve that end.

See the image direct from MS Excel:
MS Help
However, if your cells were in multiple rows, rather than columns, it is possible in a few steps to do it but only in one instance - not the entire dataset, as chandoo describes:http://chandoo.org/wp/2010/12/07/merge-cells-without-loosing-data/

Sorry, there's no other "tricks" to achieve this other than writing a VBA macro which takes longer than just using the formula I gave you.



Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This tutorial summarizes the causes behind"an unknown error prevented access to the PST File”.  It also suggests the various solutions to fix the problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question