I know how to incorporate a number from a number formatted cell into a text formatted cell, but I can't remember how to incorporate text from one cell into another text formatted cell. Here's an example of doing it with a number: ="Net weight: "&TEXT(B23,"000.00")&""
The string "Net weight" is not part of the other cell.
I want to incorporate a text string into another text formatted cell to provide information that fills in a blank in a sentence the same way I did a number as shown above. Any suggestions? I'm using Excel 2008/Mac.
Since there were no replies despite 27 views, I decided to experiment. Here's what I came up with:
Just plain incorporation of text from a string in another cell can be accomplished using this formula in the importing cell: ="Cookie Dough "&W43&": "&X43&"" . In this case, Cookie Dough is an introductory string in the cell that will then incorporate text from two other cells to complete the string that reads: "Cookie Dough Total Weight of Ingredients (grams): 3000.8615457759" Cell W43 contains the string "Total Weight of Ingredients (grams)" Then, a colon is slipped in to the string outside of the quotation marks for cell W43. Then Cell X43 contains the number "3000.8615457759" but it is imported as text without a number format so it has no comma after 3 and ten decimal places. Excel's limit is thirty decimal places. I don't know why Excel chose 10 places in this case.
The same string can be made to read like this: Cookie Dough Total Weight of Ingredients (grams): 3,000.86. This is how it is done: ="Cookie Dough "&W43&": "&TEXT(X43,"0,000.00")&"" The TEXT Function is very useful for formatting numbers imported into a text string.
Finally, some formatting can be done without the TEXT Function. For example, Percent of ingredient A: 70%, where 70% is imported from another cell, is written this way: ="Percent of ingredient A: "&P23*100&"%" When a percent is imported from another cell, it loses the percent sign and two decimal places. 30% appears in the importing cell as 0.3. That is why it is necessary to use the formula shown above with a multiplier of 100 and a % character inserted outside the quotation marks enclosing the cell reference.
This same string can be expressed using the TEXT Function: The combined string, Percent of ingredient A: 30.0%, looks like this: ="Percent of ingredient A: "&TEXT(H44,"00.0%")&"" Using the TEXT Function provides the advantage when using numbers in that the format can be adjusted quickly for the number of decimal points and for percent or dollar sign or no sign at all.
I hope this is useful to someone else. It took some work to figure it out, but it's very useful to me.
Nice job answering your own question, and thanks for sharing your efforts here!
