How to Concatenate Text in Microsoft Excel

Last Updated: May 5, 2021 Tested

Are you pulling your hair out trying to manage a large spreadsheet full of disjointed names or dates? Do you want to create form sentences that can be automatically filled in with the data from your spreadsheet? The Concatenate function is here to save the day! Follow this guide to quickly join the values of multiple cells in your Excel spreadsheet.

Steps

1
Use Concatenate to join two cells. The basic function of concatenate is to join two or more text strings together. You can join up to 255 different strings together using one concatenate command. Take the following example:
Entering the Formula

A B C

1 good bye =Concatenate(A1,B1)

The Result

A B C

1 good bye goodbye
2
Insert spaces between your joined text. If you want to join text but leave a space in between, you can add a space to the formula with quotation marks around a single space. This is especially useful for data such as first and last names. For example:
Entering the Formula

A B C

1 John Smith =Concatenate(A1," ",B1)

The Result

A B C

1 John Smith John Smith

Advertisement
3
Insert punctuation and other text between concatenated strings. As seen above, you can add spaces by placing quotation marks around a blank space in the formula. You can expand this and use quotation marks to insert any text into your concatenation. Take note of the spacing left in the quotation marks to result in a readable sentence.^{[1]
X
Research source}
Entering the Formula

A B C

1 Monday Friday =Concatenate(A1," - ",B1, ", closed weekends.")

The Result

A B C

1 Monday Friday Monday – Friday, closed weekends.
4
Concatenate a range of dates. If you have a date range that you want to join, you will need to use the TEXT function to prevent Excel from treating the dates as mathematical formulas:^{[2]
X
Research source}
Entering the Formula

A B C

1 01/14/2013 06/17/2013 =Concatenate(Text(A1,"MM/DD/YYYY")," - ",Text(B1,"MM/DD/YYYY"))

The Result

A B C

1 01/14/2013 06/17/2013 01/14/2013 - 06/17/2013
5
Use the “&” symbol as a replacement for Concatenate. The “&” performs the same function as concatenate. It can be useful for shorter formulas, but can quickly become cluttered for longer ones. Note the space in quotation marks. You must have an “&” between each value that you want to concatenate.
Entering the Formula

A B C

1 John Smith =A1&" "&B1

The Result

A B C

1 John Smith John Smith

Community Q&A

Question

I have a column full of email addresses. I want to concatenate them with semicolons between them so that I can copy them all into my email and send them all bcc. Is there a way to do this without having to treat each one individually?

Community Answer

Assume all your addresses are in column A. In B2, add the first two (=(A1&";"&A2). Then in B3, add A3 to B2 and then fill that formula down to the bottom of the list and you will have a cell with all that data.
Question

How do I create a formula in excel 2010 to place a "yes" if the condition is met and "no" if the condition is not met?

Community Answer

What you are looking for is the IF formula. Example: =IF(A1=5,Yes,No). This formula checks if the value in cell A1 is 5. If it is, the formula will output "Yes". If A1 is any value other than 5 the formula will output "No".
Question

Can you use concatenate with an IF statement in excel?

Community Answer

Yes. Below is an example of a formula I put together using nested IF statements for a spreadsheet where the output depended on 2 input cells. =IF(ISBLANK(B9),IF(ISBLANK(B10),"",B10),IF(ISBLANK(B10),B9,CONCATENATE(B9," & ",B10))). Result: if B9 and B10 are blank the output cell will show nothing; if B9 = "word" and B10 is blank; output = "word"; if B9 is blank and B10 = "text"; output = "text"; if B9 = "word" and B10 = "text"; output = "word & text".