Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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(A11,"s is "," is "),TEXT(A2,"£#,##0.00"),". Please pay now!") or, if you prefer ="The cost of your " & TEXT(A1,"#,##0") & " item" & IF(A11,"s is "," is ") & TEXT(A2,"£#,##0.00") & ". Please pay now!" -- ** John C ** "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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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!! "Glenn" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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" wrote: 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(A11,"s is "," is "),TEXT(A2,"£#,##0.00"),". Please pay now!") or, if you prefer ="The cost of your " & TEXT(A1,"#,##0") & " item" & IF(A11,"s is "," is ") & TEXT(A2,"£#,##0.00") & ". Please pay now!" -- ** John C ** "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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
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 :) -- ** John C ** "John G" wrote: 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" wrote: 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(A11,"s is "," is "),TEXT(A2,"£#,##0.00"),". Please pay now!") or, if you prefer ="The cost of your " & TEXT(A1,"#,##0") & " item" & IF(A11,"s is "," is ") & TEXT(A2,"£#,##0.00") & ". Please pay now!" -- ** John C ** "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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel: How do you format numbers in a concatenate string?
John G wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change a string of numbers in to number format | Excel Worksheet Functions | |||
Concatenate in a string | Excel Worksheet Functions | |||
Format numbers returned in the function CONCATENATE | Excel Discussion (Misc queries) | |||
Format numbers / Concatenate | Excel Discussion (Misc queries) | |||
Can i convert numbers into string format? | Excel Discussion (Misc queries) |