Write a FORMAT code in a cell 1

[Jamie2002]

How do I write code in a cell to format a number in a particular way?

What I'm doing is writing a formula which pulls in text and then inserts numbers into the text, ie

="Sales this period were -" & A50

However the number is formatted in the original cell it comes through as eg 50000.0000. I can sort out the decimals with round(x,1) but how do I apply a #,##0.0 on the number or a currency format ?

Thanks.

Jamie. Thanks

Jamie

 

[xlbo]

Hi - try this
="Sales this period were -" & text(A50,"#,##0.0&quot


HTH
~Geoff~

 

[Jamie2002]

That worked perfect, you've no idea how many times I've wanted to do that and got fed up trying.

Thank you.

Out of interest what exactly does the text function do ? and what can it be used for ? Thanks

Jamie

 

[xlbo]

The TEXT function changes a value to a formatted text representation of that value. You can use it to :
1: Turn values into text (for lookup purposes etc)
2: Do exactly what you want to do - show a representation of a number in a string but not the whole number (ie chop off DPs / convert to % etc etc. If you wanted to do some comentary that was dynamic ie you have text like
The YTD Sales are 10% higher than last year, you could use something like
="The YTD Sales are " & text((A1-B1)/B1,"%0&quot & " higher than last year"
where A1 = YTD sales this year and B1 = YTD sales last year

If A1c and B1 change weekly or monthly, the commentary will be dynamic so you don't need to change it
HTH
~Geoff~