Do you find it difficult when trying to manage a large worksheet full of separate names or dates? Do you want to create a sentence in the form of a form that can automatically be filled with data from a worksheet? The Concatenate function here is to save you time! Follow this guide to quickly combine values from multiple cells in your Excel worksheet.
Step
Step 1. Use Concatenate to merge the two cells
The basic function of concatenate is to concatenate two or more text strings together. You can concatenate 255 different strings together using a single concatenate command. Consider the following example:
Entering the Formula
| A | B | C | |
|---|---|---|---|
| 1 | good | bye | =Concatenate(A1, B1) |
Result
| A | B | C | |
|---|---|---|---|
| 1 | good | bye | goodbye |
Step 2. Enter a space between the texts you are merging
If you want to concatenate text but want a space between them, you can add a space to the formula with quotation marks around a single space. This is especially useful for data such as first name and last name. For example:
Entering the Formula
| A | B | C | |
|---|---|---|---|
| 1 | John | Smith | =Concatenate(A1, " ", B1) |
Result
| A | B | C | |
|---|---|---|---|
| 1 | John | Smith | John Smith |
Step 3. Insert punctuation and other text between concatenated strings
As seen above, you can add spaces by placing quotation marks around blank spaces in the formula. You can expand it and use quotes to insert any text into your join. Pay attention to the spaces in the quotation marks to make sentences that can be read.
Entering the Formula
| A | B | C | |
|---|---|---|---|
| 1 | Monday | Friday | =Concatenate(A1, " - ", B1, ", closed weekends.") |
Result
| A | B | C | |
|---|---|---|---|
| 1 | Monday | Friday | Monday – Friday, closed weekends. |
Step 4. Merge date ranges
If you have a range of dates that you want to combine, you must use the TEXT function to prevent Excel from treating the dates as mathematical formulas:
Entering the Formula
| A | B | C | |
|---|---|---|---|
| 1 | 2013-14-01 | 2013-17-06 | =Concatenate(Text(A1, "MM/DD/YYYY"), " - ", Text(B1, "MM/DD/YYYY")) |
Result
| A | B | C | |
|---|---|---|---|
| 1 | 2013-14-01 | 2013-17-06 | 2013-14-01 - 2013-17-06 |
Step 5. Use the "&" symbol instead of Concatenate
"&" performs the same function as Concatenate. This can be useful for short formulas, but can get messy quickly for long formulas. Notice the spaces in the quotes. You must enter "&" between each value you want to combine.
Entering the Formula
| A | B | C | |
|---|---|---|---|
| 1 | John | Smith | =A1&" "&B1 |
Result
| A | B | C | |
|---|---|---|---|
| 1 | John | Smith | John Smith |
