An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Previous Thread
Next Thread
Print Thread
Incorporating text from another cell using Excel
#5499 10/30/09 10:13 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
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.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Incorporating text from another cell using Exc
JoBoy #5574 11/05/09 04:39 AM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
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.



Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Incorporating text from another cell using Exc
JoBoy #5579 11/05/09 02:13 PM
Joined: Aug 2009
Likes: 1
Moderator
Offline
Moderator

Joined: Aug 2009
Likes: 1
Nice job answering your own question, and thanks for sharing your efforts here! laugh


alternaut moderator
Re: Incorporating text from another cell using Exc
alternaut #5582 11/05/09 11:11 PM
Joined: Aug 2009
JoBoy Offline OP
OP Offline

Joined: Aug 2009
Originally Posted By: alternaut
Nice job answering your own question, and thanks for sharing your efforts here! laugh


Thank YOU. smile


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Moderated by  alternaut, dianne, dkmarsh 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.4.33 Page Time: 0.017s Queries: 23 (0.011s) Memory: 0.5858 MB (Peak: 0.6450 MB) Data Comp: Zlib Server Time: 2024-03-29 04:36:27 UTC
Valid HTML 5 and Valid CSS