Excel Concatenate Strings: Formula Examples

These Excel concatenate strings formula examples:

• Help you join multiple strings.
• Cover solutions for both of the following groups of Excel versions:
  • Excel versions with the CONCAT function (newer Excel versions, including Excel 365).
  • Excel versions without the CONCAT function (older Excel versions).

Excel Concatenate Strings Formula Template

Excel Concatenate Strings With the CONCAT Function

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-strings/

=CONCAT(StringsToConcatenate)

For these purposes, StringsToConcatenate are the strings you concatenate. As a general rule: Specify the strings you concatenate as one of the following:

• A string, wrapped in double quotes. For example, “Concatenate string”.
• A cell or cell range reference. For example:
  • A1; or
  • A1:B1.

Use commas to separate the arguments of the CONCAT function.

Excel Concatenate Strings Without the CONCAT Function

The Excel concatenate strings (without the CONCAT function) template below relies on several cells (1 cell for each string you concatenate). Out of these cells:

• The last cell's output contains the full string, concatenating all strings; and
• All other cells (excluding the last one) are helper cells.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-strings/

'Concatenation cell 1
=StringToConcatenate1

'Concatenation cell 2
=ConcatenationCell1&StringToConcatenate2

'Concatenation cell 3
=ConcatenationCell2&StringToConcatenate3

'...

'Concatenation cell n
=ConcatenationCellN-1&StringToConcatenateN

For these purposes:

• ConcatenationCell# (ConcatenationCell1 to ConcatenationCellN-1) is a reference to the previous (n-1) helper cell you create.
• StringToConcatenate# (StringToConcatenate1 to StringToConcatenateN) is the applicable (original) string to concatenate.

Excel Concatenate Strings Example Formulas

The Excel concatenate strings example formulas below join the contents of cells A7 to B21.

• Cells in column A (A7 to A21) store a string of the form String#.
• Cells in column B (B7 to B21) store a space.

Excel Concatenate Strings With the CONCAT Function Example Formula

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-strings/

=CONCAT(A7:B21)

The first image at the top of this post shows the results I obtain with this Excel concatenate strings example formula.

Excel Concatenate Strings Without the CONCAT Function Example Formulas

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-strings/

'Concatenation cell 1 (cell D7)
=A7&B7

'Concatenation cell 2 (cell D8)
=D7&A8&B8

'Concatenation cell 3 (cell D9)
=D8&A9&B9

'Concatenation cell 4 (cell D10)
=D9&A10&B10

'Concatenation cell 5 (cell D11)
=D10&A11&B11

'Concatenation cell 6 (cell D12)
=D11&A12&B12

'Concatenation cell 7 (cell D13)
=D12&A13&B13

'Concatenation cell 8 (cell D14)
=D13&A14&B14

'Concatenation cell 9 (cell D15)
=D14&A15&B15

'Concatenation cell 10 (cell D16)
=D15&A16&B16

'Concatenation cell 11 (cell D17)
=D16&A17&B17

'Concatenation cell 12 (cell D18)
=D17&A18&B18

'Concatenation cell 13 (cell D19)
=D18&A19&B19

'Concatenation cell 14 (cell D20)
=D19&A20&B20

'Concatenation cell 15 (cell D21)
=D20&A21&B21

The second image (a GIF) at the top of this post shows:

• How I create the Excel concatenate strings example formula; and
• The results I obtain with this Excel concatenate strings example formula.

Excel Concatenate Strings Explanation

The CONCAT function:

• Concatenates multiple strings (including strings stored in cells); but
• Doesn't allow you to specify a delimiter.

If you're working with an Excel version where the CONCAT function is available, use the CONCAT function to create an Excel concatenate strings formula.

Older Excel versions lack an alternative that's as efficient as CONCAT to create an Excel concatenate strings formula. The most common alternatives involve repeatedly entering the strings (or references to the cells with the strings) you concatenate.

For example, the ampersand text concatenation operator (&):

• Concatenates strings; and
• Returns a single string with the concatenated items.

Therefore, you can (theoretically) use the ampersand text concatenation operator (&) to create an Excel concatenate strings formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-strings/

=StringToConcatenate1&StringToConcatenate2&StringToConcatenate3&...&StringToConcatenateN

Implementing this Excel concatenate strings formula template when working with larger data sets is:

• Time consuming;
• Tedious; and
• Error prone.

The Excel concatenate strings (without the CONCAT function) template I explain above allows you to:

• Use the ampersand text concatenation operator (&) to create an Excel concatenate strings formula; while
• Addressing (to the extent possible) some of the limitations I describe above.

The CONCAT function and the ampersand text concatenation operator (&) are not the only ways to concatenate items. You can find more Excel concatenate formula examples in the More Excel Concatenate Formula Examples section.

More Excel Concatenate Formula Examples

This formula example is part of a more comprehensive series of Excel concatenate formula examples.

• Excel Concatenate Strings: Click here to open.
• Excel Concatenate Number and String: Click here to open.
• Excel Concatenate Date: Click here to open.
• Excel Concatenate with Space: Click here to open.
• Excel Concatenate New Line: Click here to open.
• Excel Concatenate Double Quote: Click here to open.
• Excel Concatenate Multiple Cells with Comma: Click here to open.

More Excel Training Materials and Resources

You can find more Excel Tutorials (including other formula examples) in the organized Tutorials Archive: Click here to visit the Archives.

If you want to ease all your operations and processes, you may be interested in Someka's ready-to-use Excel and Google Sheets templates. Someka's template collections cover, among others:

• Project Management;
• Human Resources;
• Sales and Marketing;
• Inventory Management;
• Calendars and Timesheets;
• Budgets; and
• Invoices.

Click here to learn more about Someka's Excel and Google Sheets templates