Excel: How do you format numbers in a concatenate string?

[John G]

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?

 

[John C]

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 G]

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!!

 

[Glenn]

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 C]

Actually, I was very close to accurate on what you were seeking.
Understanding I have the US regional settings for currency, but I think you
could modify as you need to.
A3: =CONCATENATE("The cost of your ",TEXT(A1,"#,##0")," item",IF(A1>1,"s is
"," is "),TEXT(A2,"£#,##0.00"),". Please pay now!")

or, if you prefer
="The cost of your " & TEXT(A1,"#,##0") & " item" & IF(A1>1,"s is "," is ")
& TEXT(A2,"£#,##0.00") & ". Please pay now!"

 

[John G]

Thanks Glenn & John C
OK this is it - this works.

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

Final query!
Where can I discover the other formats I may wish to provide, in future, as
the expression [$£-809] is pure gobbledeegook to me!!

The answer provided does what you want.

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

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 G]

I like the plural twist John C - very neat!! Accuracy is always mentally
stimulating - even when it's getting late in the working day over here in
London, UK!!

 

[John C]

Don't forget to check the little handy dandy YES button below to indicate
your question has been answered satisfactorily, and thanks for the feedback

 

[Glenn]

Final query!
Where can I discover the other formats I may wish to provide, in future, as
the expression [$£-809] is pure gobbledeegook to me!!

Look at "Create or delete a custom number format" in the help file.

Also, select Format / Cells / Number and pick a number format you are interested
in. Then, without exiting the dialog, click on Custom at the bottom of the
Category list.