Format connected cells: lose of number format

Try this formula

=IF(Units="","",TEXT(Units*(1+G22),"#,##0")&Measurment)

BTW, Measurment is spelt Measurement

Thanks Bob,
I have tried to do work with the same formula on the currency column (Adding text formula) , but this doesnt work.
I am now uploading the real model, rather than the dummy
would appreciate your help... see cells H32:h37.
I am suspecting that this is something to do with the text formla

Try starting with this

=TEXT(TRIM(SUBSTITUTE($H32,"$",""))*(1+I$30)*Units-(unit_cost*Units*(1+I$30))-Profit,"#.##0;-#,##0")&measurement

not sure where this formula needs to go?
Just to clarify:
I am looking to create a formula (in cells H32: H37) that will combine the currency symbol in cell g12

In I32, down and across.

ohhh. now I got it. thank you , this is very nice of you, but I am trying to do something else ...
I am trying to format cells H32:37 (actully all the way down to H46) so they will show the currency format which is in cell G12 (the currency may change) withough damaging the calculations which are in the table. .

Any idea how to do so?

OK, just change measurement to Currency in the formula. Also, change the hard-code $

=TEXT(TRIM(SUBSTITUTE($H32,Currency,""))*(1+I$30)*Units-(unit_cost*Units*(1+I$30))-Profit,"#.##0;-#,##0")&Currency

this formula works on what inside the table. I want to work on what is on the axis (cells H32:H46)
So for example cell H39 will show 100$, but the $ will come from what is in cell G12. rather than from the formating of the cell.
I am not sure if this can be done - I tought that custom formating can solve this , but not sure this functionality exist ...

Originally Posted by SwissExcel

this formula works on what inside the table. I want to work on what is on the axis (cells H32:H46)
So for example cell H39 will show 100$, but the $ will come from what is in cell G12. rather than from the formating of the cell.
I am not sure if this can be done - I tought that custom formating can solve this , but not sure this functionality exist ...

I am picking it up from G12, that cell is named Currency. You can't reference a cell in n umber format, but you can set it with the TEXT function as I show.

Maybe it would help if you showed what you want the output to look like. ie. $107/Kg