Merge cells without losing data! How to do that?

I got an excel file, with below data:
A1: "Contains Data 1"
A2: "Contains Data 2"
A3: "Contains Data 3"
A4: "Contains Data 4"
A5: "Contains Data 5"

All are text.

A1:A5 was merged, displayed data in A1: "Contains Data 1"

It is usual, if A2:A5 were deleted.

But I tried :
B1=A1
B2=A2
...

It said that A2:A5 are not losing data, though they were merged.

Do you know how to do that?

Macro can do it. Copy data to B column and then merge the cells.

No, not macro. Column B is helper column, to test whether column A has data or not.

Set Calculation options to Manual before merge cells.

Hi Bebo,

Text to columns.

Hope this helps.

Paste the merge formatting from somewhere else, either using copy and paste special, or the format painter, and the values will remain intact in each cell.