Formula Text String: Formatting Text and Numbers?

[Guest]

Excel 2003

If I havea text string formula and I insert a number. How do I get the
number to be formated with the comma
for "thousands"
[A1]
= "I want to purchase " &C2& " candy bars from the grocery store"

= "I want to purchase " 10,000 " candy bars from the grocery store"

I tried TEXT(A1,#,###) and doesnt work
Also, I want to put only a couple of words in bold font?. For example the
word "purchase".

 

[Guest]

a partial response is to use TEXT(A1,"#,###") with quote marks
making part of it be bold would require code.

 

[Rick Rothstein \(MVP - VB\)]

If I havea text string formula and I insert a number. How do I get the

number to be formated with the comma
for "thousands"
[A1]
= "I want to purchase " &C2& " candy bars from the grocery store"

= "I want to purchase " 10,000 " candy bars from the grocery store"

I tried TEXT(A1,#,###) and doesnt work

Are you allowed to change the formula in A1?

A1: = "I want to purchase " &TEXT(C2,"#,###")& " candy bars from the grocery
store"

Rick

 

[Guest]

Not exactly sure what you mean. The formula in A1 can change, my goal is to
have a setence in which the number of candy bars changes based on another
cells results. Even though that other cell is formated correctly (number with
a common in the thousands) it doesn't carry over. I also need one of the
words in the setence to be in bold font. Open to ideas.

If I havea text string formula and I insert a number. How do I get the
number to be formated with the comma
for "thousands"
[A1]
= "I want to purchase " &C2& " candy bars from the grocery store"

= "I want to purchase " 10,000 " candy bars from the grocery store"

I tried TEXT(A1,#,###) and doesnt work

Are you allowed to change the formula in A1?

A1: = "I want to purchase " &TEXT(C2,"#,###")& " candy bars from the grocery
store"

Rick

 

[Peo Sjoblom]

It will carry over if you use the formula that was provided by Rick


--
Regards,

Peo Sjoblom

Not exactly sure what you mean. The formula in A1 can change, my goal is
to
have a setence in which the number of candy bars changes based on another
cells results. Even though that other cell is formated correctly (number
with
a common in the thousands) it doesn't carry over. I also need one of the
words in the setence to be in bold font. Open to ideas.

If I havea text string formula and I insert a number. How do I get the
number to be formated with the comma
for "thousands"
[A1]
= "I want to purchase " &C2& " candy bars from the grocery store"

= "I want to purchase " 10,000 " candy bars from the grocery store"

I tried TEXT(A1,#,###) and doesnt work

Are you allowed to change the formula in A1?

A1: = "I want to purchase " &TEXT(C2,"#,###")& " candy bars from the
grocery
store"

Rick

 

[Ron Rosenfeld]

Excel 2003

If I havea text string formula and I insert a number. How do I get the
number to be formated with the comma
for "thousands"
[A1]
= "I want to purchase " &C2& " candy bars from the grocery store"

= "I want to purchase " 10,000 " candy bars from the grocery store"

I tried TEXT(A1,#,###) and doesnt work
Also, I want to put only a couple of words in bold font?. For example the
word "purchase".

If the number of candy bars is in C2, then:

A1:
= "I want to purchase " &TEXT(C2,"#,###")
& " candy bars from the grocery store"

will format the value as you describe.

However to bold the word purchase, you will need to use a macro that will place
a text string into A1, rather than a formula. You cannot, in Excel, bold part
of a formula result.

Here's one way:

Right click on the sheet tab.
Select "View Code"
Paste the following into the window that appears.

Type some number into C2.

You will need to modify this to your specific requirements.

=========================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const s1 As String = "I want to purchase "
Const s2 As String = " candy bars from the grocery store."
Const sFmt As String = "#,###"
Const sTextToBold As String = "purchase"

With Range("A1")
.Value = s1 & Format(Range("c2"), sFmt) & s2
.Characters(InStr(1, Range("a1").Text, sTextToBold),
Len(sTextToBold)).Font.Bold = True
End With

End Sub
========================================
--ron