Putting the value of another cell in an Excel text cell.

Ok, I have totally lost it.  
Simple example of what I need.
In A1 I have a color, say blue
In A2 I want to end up with the following text: The blue cow jumped over the fence.
A2 looks like this...
="The "&A1" "cow jumped over the moon."
I am getting an error.  Why?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

="The " & A1 & " cow jumped over the moon."
ProfessorJimJamMicrosoft Excel ExpertCommented:
you were missing the second ampersand  ="The "&A1& "cow jumped over the moon."
SteveCost AccountantCommented:
You could also use CONCATENATE to make it easier to read:

=CONCATENATE("The ",A1," cow jumped over the moon.")
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Bill GoldenExecutive Managing MemberAuthor Commented:
Building on that example...
Simple example of what I need.
In A1 I have a name, say Bill
In A2 I want to end up with the following text: It was Bill's cow who jumped over the moon.
A2 looks like this...
="It was "&A1&""'s cow that jumped over the moon."
I am getting an error.  Why?
Saqib Husain, SyedEngineerCommented:
In future if you have such a problem you can try doing it in steps to narrow down to the problem

="The "
="The " & A1
="The " & A1 & " cow jumped over the moon."
Saqib Husain, SyedEngineerCommented:
You do not need two quotes together

="It was "&A1&"'s cow that jumped over the moon."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Last part, I promise...
Simple example of what I need.
In A1 I have a name, say Bill
In A2 I want to end up with the following text: It was Bill's cow who jumped over the "blue" moon.
A2 looks like this...
="It was "&A1&"'s cow that jumped over the "blue" moon."
I realize I need to do something to use something around the quotes that surround the word blue, but what?
Rory ArchibaldCommented:
Double the quotes:
="It was "&A1&"'s cow that jumped over the ""blue"" moon."
Kanti PrasadCommented:
As you left out an & on the right side you are getting the error. You can use TRIM to ensure that you are only taking the color and not any additional spaces on either side

="The "&TRIM(A1)&" cow jumped over the moon."
Bill GoldenExecutive Managing MemberAuthor Commented:
Got it.  I was obviously born missing a syntax gene, but learning.  Thanks everyone.
SteveCost AccountantCommented:
Try using SUBSTITUTE:

In A1: Bill
In B1: It was [name]'s cow that jumped over the "blue" moon.
In C1: =SUBSTITUTE(B1,"[name]",A1)

This will replace [name] in the sentence of B1 with the contents of A1.
Kanti PrasadCommented:
All you need is an &TRIM(A?)&

" your info "  &TRIM(A?)&  " your info" & TRIM(A?)& "your info"
replace ? with the cell number

So for eg  if you have Blue in cell A1 and Bill in cell A3 then in A2


="It was" &TRIM(A3)& "cow who jumped over the " &TRIM(A1)&" moon."
Bill GoldenExecutive Managing MemberAuthor Commented:
I have been "hoisted by my own pitard" since this is part of a paragraph and that makes it too long.  Starting a new question.
Rory ArchibaldCommented:
"petard" actually. :)
Bill GoldenExecutive Managing MemberAuthor Commented:
Your right of course.  Not only is my sense of syntax bad, my spelling seems to be adrift!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.