Maintaining number format with combining of cells

[punit83]

Hello !

I have a excel sheet with 3 column data

1st column = has sr no.
2nd column = has no. of unit
3rd coloum = has individual total amount value

Eg:-

<tbody>
[TD="class: xl66"]#[/TD]
[TD="class: xl66"]Unit[/TD]
[TD="class: xl67, width: 103"]TOTAL AMT[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl70, align: center"]100[/TD]
[TD="class: xl68, align: right"]100.00[/TD]

[TD="class: xl66, align: center"]2[/TD]
[TD="class: xl70, align: center"]200[/TD]
[TD="class: xl68, align: right"]400.00[/TD]

[TD="class: xl66, align: center"]3[/TD]
[TD="class: xl70, align: center"]300[/TD]
[TD="class: xl68, align: right"]900.00[/TD]

[TD="class: xl66, align: center"]4[/TD]
[TD="class: xl70, align: center"]400[/TD]
[TD="class: xl68, align: right"]1,600.00[/TD]

[TD="class: xl66, align: center"]5[/TD]
[TD="class: xl70, align: center"]500[/TD]
[TD="class: xl68, align: right"]2,500.00[/TD]

[TD="class: xl66, align: center"]6[/TD]
[TD="class: xl70, align: center"]600[/TD]
[TD="class: xl68, align: right"]3,600.00[/TD]

[TD="class: xl66, align: center"]7[/TD]
[TD="class: xl70, align: center"]700[/TD]
[TD="class: xl68, align: right"]4,900.00[/TD]

[TD="class: xl66, align: center"]8[/TD]
[TD="class: xl70, align: center"]800[/TD]
[TD="class: xl68, align: right"]6,400.00[/TD]

[TD="class: xl66, align: center"]9[/TD]
[TD="class: xl70, align: center"]900[/TD]
[TD="class: xl68, align: right"]8,100.00[/TD]

[TD="class: xl66, align: center"]10[/TD]
[TD="class: xl70, align: center"]1,000[/TD]
[TD="class: xl68, align: right"]10,000.00[/TD]

</tbody>

<tbody>
[TD="class: xl68"]Total Count[/TD]
[TD="class: xl66, align: right"]10[/TD]

[TD="class: xl68"]Total Unit[/TD]
[TD="class: xl67, align: right"]5,500.00[/TD]

[TD="class: xl68"]Total Amount[/TD]
[TD="class: xl67, align: right"]38,500.00[/TD]

</tbody>

I want all these 3 details in one cell with maintaining number format.

i am using these formula
="No : "&SUBTOTAL(3,Tablex[Unit])&" | "&"Unit : "&SUBTOTAL(9,Tablex[Unit])&" | "&"Total Amt : "&SUBTOTAL(9,Tablex[TOTAL AMT])

and result is like this

<tbody>
[TD="class: xl65"]No : 10 | Unit : 5500 | Total Amt : 38500[/TD]

</tbody>

( No comma separator)



(i am newbie so maybe there will be mistake in formula or even if anyone has easier formula than it will help me a lot)


Thanks in advance.

 

[FormR]

Hi, welcome to the forum!

You can try this:

="No : "&SUBTOTAL(3,Tablex[Unit])&" | "&"Unit : "&TEXT(SUBTOTAL(9,Tablex[Unit]),"#,##0")&" | "&"Total Amt : "&TEXT(SUBTOTAL(9,Tablex[TOTAL AMT]),"#,##0.00")

 

[punit83]

Thank you so muchhhhhhhh......... solved

 

[punit83]

Hello FormR,

i have new question please guide me.

I have this series


0.18 - 0.22
0.23 - 0.29
0.30 - 0.39
0.40 - 0.49
0.50 - 0.69
0.70 - 0.89
0.90 - 0.99
1.00 - 1.49
1.50 - 1.99

now for example i want to use formula that as i typed number in a1=any random number from 0.30 to 39 than result in b1 should be 0.30-0.39

is there any formula without vba ?


thanks in advance.