Inserting a formula into a text string
 

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Answer: Inserting a formula into a text string
 

Yes, you can definitely insert a formula or reference a cell on another worksheet in the middle of a text string in Excel. Here's how you can do it:

1. Start by typing the text string as you normally would, including the placeholder where you want the formula or reference to go. For example, you might type: "XYZ company will pay $______ of your shipping costs."
2. Click on the cell where you want to insert the formula or reference. This will add the cell reference to the formula bar at the top of the screen.
3. Copy the cell reference by pressing Ctrl+C or by right-clicking on the cell and selecting "Copy."
4. Go back to your text string and place your cursor where you want the cell reference to go.
5. Type an equal sign (=) to indicate that you're entering a formula.
6. Paste the cell reference into the formula by pressing Ctrl+V or by right-clicking and selecting "Paste."
7. Finish the formula by adding any necessary operators or functions. For example, if you want to add 10% to the shipping cost, you might type "=B2*1.1" (assuming that the cell reference you copied was B2).
8. Press Enter to complete the formula. The result of the formula will be displayed in the cell, but the text string will still show the placeholder.
9. To include the result of the formula in the text string, simply click on the cell with the formula and copy it as you did before.
10. Go back to your text string and replace the placeholder with the formula result by pasting it in.

That's it! Your text string should now include the result of the formula or cell reference.

Inserting a formula into a text string
 

="XYZ company will pay $"&Sheet6!A1&" of your shipping costs"
--
David Biddulph

"Courie" wrote in message
...
Is there a way to insert a formula or reference a cell on another
worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Inserting a formula into a text string
 

Is there a way to insert a formula or reference a cell on another
worksheet in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of
your shipping costs... Where the blank was a reference from another
sheet.

One way is to use a formula like this:
="XYZ company will pay "&TEXT(Sheet2!A1,"$0.00")&" of your shipping costs"

The ampersand does concatenation of strings.

Inserting a formula into a text string
 

Another way is to use

="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs"

"Courie" wrote:

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Inserting a formula into a text string
 

I have tried all of your suggestions--Thanks, by the way--but each of them
simply prints the verbiage of the function, not the result.

What could I be doing wrong?
--
Thanks,

Courie


"daddylonglegs" wrote:

Another way is to use

="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs"

"Courie" wrote:

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Inserting a formula into a text string
 

Maybe your cell containing the formula is formatted as Text. Use Format
| Cells | Number (tab) and choose General. Then double-click the cell
as if to edit it in order to activate the change in format.

Hope this helps.

Pete

Courie wrote:

I have tried all of your suggestions--Thanks, by the way--but each of them
simply prints the verbiage of the function, not the result.

What could I be doing wrong?
--
Thanks,

Courie


"daddylonglegs" wrote:

Another way is to use

="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs"

"Courie" wrote:

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Inserting a formula into a text string
 

Thanks for your suggestion Pete, unfortunately, that didn't work either. The
cells were already set to General and I set them to Text, then re-set to
General and recalculated the sheet, but there was no change. The forumla
still prints and views as text.
--
Thanks,

Courie


"Pete_UK" wrote:

Maybe your cell containing the formula is formatted as Text. Use Format
| Cells | Number (tab) and choose General. Then double-click the cell
as if to edit it in order to activate the change in format.

Hope this helps.

Pete

Courie wrote:

I have tried all of your suggestions--Thanks, by the way--but each of them
simply prints the verbiage of the function, not the result.

What could I be doing wrong?
--
Thanks,

Courie


"daddylonglegs" wrote:

Another way is to use

="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs"

"Courie" wrote:

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.
--
Thanks,

Courie

Found this thread and also managed to find a solution to your problem, Courie. In my case, I was trying to generate some unique ID's for testing purposes. At any rate, I just wanted to push the ROW # of a cell into a String, copy/paste, and have it resolve itself. Here's how I managed to do it:

...are you using the "=" (equals sign) at the beginning of the text entered, marking the cell as a formula? I had similar trouble before adding this symbol - incredibly important.

Good luck,
Zac

Quote:

Originally Posted by Courie (Post 403489) Thanks for your suggestion Pete, unfortunately, that didn't work either. The cells were already set to General and I set them to Text, then re-set to General and recalculated the sheet, but there was no change. The forumla still prints and views as text. -- Thanks, Courie "Pete_UK" wrote: Maybe your cell containing the formula is formatted as Text. Use Format | Cells | Number (tab) and choose General. Then double-click the cell as if to edit it in order to activate the change in format. Hope this helps. Pete Courie wrote: I have tried all of your suggestions--Thanks, by the way--but each of them simply prints the verbiage of the function, not the result. What could I be doing wrong? -- Thanks, Courie "daddylonglegs" wrote: Another way is to use ="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs" "Courie" wrote: Is there a way to insert a formula or reference a cell on another worksheet in the middle of a text string. For example if I wanted to say: XYZ company will pay $___________ of your shipping costs... Where the blank was a reference from another sheet. -- Thanks, Courie