View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glenn Glenn is offline
external usenet poster
 
Posts: 1,240
Default Excel: How do you format numbers in a concatenate string?

The answer provided does what you want.

A3=CONCATENATE("The cost of your ",A1," items is
",TEXT(A2,"[$£-809]#,##0.00"),". Please pay now!")


John G wrote:
Thanks for your speedy reply John C but I was wasn't very clear in my question!

Right assume this:
Cell A1 = 20
Cell A2 = £1,799.50

Then in cell A3 I am wishing to report:
Cell A3 = The cost of your 20 items is £1,799.50. Please pay now!

I am fiddling with the formula...
Cell A3 ="The cost of your "&A1&" items is £"&A2&" . Please pay now!"
Whilst A1 picks up correctly by default a "general" format - the result from
A2 does not provide the requisite thousands comma delimeter.

Is this easy....? I suspect so!!


"John C" wrote:

I think this will work for you:
=TEXT(string,"format")
You could add -- in front of TEXT to ensure it is viewed as numeric by excel
if you need it too. For example, say you wanted to state something like
The cost of the product is $17.99. Thank you for your order.

You could do the following:
=CONCATENATE("The cost of your product ",TEXT(cost,"$#,##0.00"),". Thank you
for your order.")

If this isn't what you were looking for, reply back and give more details.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"John G" wrote:

I have a concatenate string with various numerical entries. I want to format
the numercial returns with varying formats. What function do I need to
incorporate into my string surrounding each numeric & pickup?