Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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

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.
0
MSGK161091
Asked:
MSGK161091
  • 3
  • 2
2 Solutions
 
dlmilleCommented:
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.

Cheers,

Dave
0
 
MSGK161091Author Commented:
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.
0
 
dlmilleCommented:
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.

Dave
0
 
MSGK161091Author Commented:
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.

Cheers!
0
 
dlmilleCommented:
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.

Cheers,

Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now