# Format overlapping cells

#### sythong

##### Active Member
Greetings to my excel pals again. Need help
on the following worksheet format which is
prepared by a non-excel user:

A BC DE
1 RM100 RM100

Column A shows Qty
Column BC is merge to show RM100
Column DE is merged to show RM100

Actually 3 columns would suffice, i.e.
Col A is for qty, B for unit price and
C for Sub-Total. For information purpose
the above merged format is ok but now
I am unable to do any calculation on this.

How can I untangle this so as to carry out
calculations.

Thanks

Thong

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Why is it merged? What would happen if you unmerge it?

RAM

How can I untangle this so as to carry out
calculations
What calculations are you trying to do that don't work with the merged cells? Perhaps you could give us a bit more sample data and what you are trying to do with it?

RAM

I think the person doing the typing did
not envisage that it would be used for calculation.
Instead it was more of an aesthetic purpose.
I think I should have unmerged the cells!!!

Peter
I am trying to total up all the data but because the
cells are merged I am not sure how I can sum
the merged cell. In addition the RM sign is not
done throuch cell formatting but type in.

Anyway I will unmerged the cells and reformat
it.

Thanks

sythong

I am not suggesting that you leave the cells merged, but I believe you CAN if you want. I also assume that it is only the Quantity and unit price that is typed in and that the sub-total could be calculated.

See if this is any help.

Columns B and C are merged, as are columns D and E.
Formula in merged cell D1:E1 (copied down to row 5) is:
=A1*SUBSTITUTE(B1,"RM","")

Formula in merged cell D6:E6 is:
=SUM(D1:E5)
Mr Excel.xls
ABCDE
11RM100100.00
23RM50150.00
34RM100400.00
41RM200200.00
53RM50.50151.50
6Total1001.50
Sheet1

I haven't found merged cells recommended by anyone on this board that really knows Excel, but I found that I sometimes need them to make my forms look decent. They are sometimes a drag when you reformat your forms when you under some conditions need to unmerge the cells and merge them back again and VBA will not work with merged cells.

I can probably recommend you merge as little cells as possible.

Edit: Peter gave you a good solution to keep them in this case.

Edit 2: If you want to place the RM in one column and the cost in another you can use Data - Text to Columns, select Fixt Width (in this case).

HTH

RAM

Edit 2: If you want to place the RM in one column and the cost in another you can use Data - Text to Columns, select Fixt Width (in this case).
Or, to get rid of the RM altogether, select column B then Edit|Replace...|Find what: RM|Replace with:
then click Replace All

That's right. You can even do the same (delete RM) using Data - Text to Columns. In the third step select RM, select Do not import (skip) and Finish.

I haven't seen your form, but I guess I would keep RM in one column, right align, and the cost, left align, in another. It keeps the formulas simple.

Hope you get your form together.

RAM

I just found another topic that will probably be the best solution for you sythong: http://www.mrexcel.com/board2/viewtopic.php?t=182243

Use a Custom format and don’t enter the RM part, just the cost and the RM will prefix the cost automatically. Then use the formulas you normally use.

Follow the step in the above topic and you have a solution. Select all the cells with RM and then do the Custom Format. If you later want to copy that format use the Format Painter.

HTH

RAM

Replies
0
Views
370
Replies
2
Views
151
Replies
0
Views
154
Replies
4
Views
395
Replies
0
Views
167

### Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back