Merge text in Excel (with pictures)

Table of contents:

Merge text in Excel (with pictures)
Merge text in Excel (with pictures)
Anonim

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

Popular by topic