Is there a way to combine words and values in a cell in Excel?

Introduction

I answered a question on Quora that is very similar to the title of this page. The answer is, yes, you can combine text and values in Excel and this page walks you through one way to do that and if you would like to learn how to combine a word or phrase into a cell containing another phrase, look here.

How it's done!

This is something I do frequently and once you get the hang of it, off you go to the stars!

I am giving you a few answers here: actually, they are just variations of each other.

Imagine the following entries in the following cells

A1=The price of eggs is

A2=15

A3=per dozen

And I want cell B1 to read, The price of eggs is 15 per dozen

B1=A1&A2&A3, which reads, The price of eggs is15per dozen

Do you notice there is no space between is and 15 and 15 and per. Here is how to fix that:

B1=A1&” “&A2&” “&A3

Compare the two results

Alternative 1

You could do it this way if you want to create the text on the fly, typing as you go:

B1=”The price of eggs is “&A2&”per dozen”

You can type anything between the double inverted commas … try it!

Alternative 2

What happens when A2 contains, say =1000/3? This will show as 333.33333333333 in cells A2 and B1, of course. And when we try our text and number embedding, we find this:

The price of eggs is 333.3333333333333 per dozen

You don’t want that, so do this

B1=A1&" "&TEXT(A2,"0.0000")&" "&A3

Which gives you … 333.3333 … exactly four decimal places. Change 0.0000 to 0.00 and see what happens

Imagine now that A2=10000/3 = 3333.33333333333, change B1 to read

B1=A1&" "&TEXT(A2,"#,000.0000")&" "&A3 to see … 3,333.3333 …

Alternative 3

If your value is a percentage …

B1=A1&" "&TEXT(A2,"0.00%")&" "&A3

Alternative 4

Imagine your value is a date

B1=A1&" "&TEXT(A2,"dd/mm/yyyy")&" "&A3 or … mm/dd/yyyy … or … ddd/mmmm/yy …

Friendly word of warning

If your system is like mine, when I copy those formulas from here to my version of Excel, it uses a different character for the “ character and gives me an error. If it happens, you will see it clearly and to solve the problem, just delete and replace every “ from here with the “ in Excel and that should work!

Duncan Williamson

4th October 2022