Do you hate bulky spreadsheets with names and data that belong together in different fields? Do you want to create a form that can be automatically filled with data from your spreadsheet? Then the CONCATENATE function can come in handy! Follow this guide to quickly merge the values of different cells into an Excel spreadsheet.
Steps
Step 1. Use CONCATENATE to merge two cells
The basic function of CONCATENATE is to concatenate two or more lines of text. You can concatenate up to 255 different strings with the CONCATENATE TEXT command. An example:
Enter the following formula
|
a |
B |
C |
| 1 |
good |
tomorrow |
=CONCATENATE(A1, B1) |
The result
|
a |
B |
C |
| 1 |
good |
tomorrow |
good morning |
Step 2. Insert spaces automatically
If you want to concatenate text, but keep a space, you can add a space to the formula using quotes. This is especially useful for a combination of first and last names. An example:
Enter the following formula
|
a |
B |
C |
| 1 |
Hans |
Miller |
=CONCATENATE(A1, " ", B1) |
The result
|
a |
B |
C |
| 1 |
Hans |
Miller |
Hans Molenaar |
Step 3. Insert periods and other punctuation marks between merged text
As was already clear above, it is possible to insert a space using quotation marks. This is possible for any text you want to merge. Note the spaces before and after inserted characters or text.
Enter the following formula
|
a |
B |
C |
| 1 |
Monday |
Friday |
=CONCATENATE(A1, " - ", B1, ", closed on weekends.") |
The result
|
a |
B |
C |
| 1 |
Monday |
Friday |
Monday – Friday, closed on weekends. |
Step #4. Merge a series of dates
If you have a series of dates that you want to merge, you need the TEXT function to prevent Excel from treating the dates as formulas:
Enter the following formula
|
a |
B |
C |
| 1 |
2013-01-14 |
2013-06-17 |
=CONCATENATE(TEXT(A1, "DD/MM/YYYY"), " - ", TEXT(B1, "DD/MM/YYYY")) |
The result
|
a |
B |
C |
| 1 |
2013-01-14 |
2013-06-17 |
2013-01-14 - 2013-06-17 |
Step 5. Use the “&” symbol as a substitute for CONCATENATE
“&” returns the same result as the CONCATENATE function. It can be useful for shorter formulas, but longer formulas can look cluttered. Also note the quotation marks around a space. You must put a “&” between all the values you want to merge.
Enter the following formula
|
a |
B |
C |
| 1 |
Hans |
Miller |
=A1&" "&B1 |
The result
|
a |
B |
C |
| 1 |
Hans |
Miller |
Hans Molenaar |
