How to Use the CONCAT Function in Excel – 7 Examples

The CONCAT function can be used to concatenate different values and ranges of cells. It can handle dynamic data from different sources.

Download Practice Workbook

Overview of the CONCAT Function

The CONCAT function takes multiple arguments as input. The arguments can be of various data types. It joins all arguments and creates a string.

Syntax:

Arguments:

Text1 is a required argument. This is the first value to be joined. It can be a text, number, symbol, or cell reference. The rest of the arguments are optional and have similar characteristics.

Example 1 – Use the CONCAT Function with Texts

There are different names in the dataset.

Add the domain (@gmail.com) to these names to get the email addresses.

• Go to C5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

Example 2 – Use the CONCAT Function with Numbers

Add the country code (+1) before the phone numbers.

• Go to C5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

Example 3 – Apply the CONCAT Function to a Range of Cells

Use the CONCAT function to get the IDs along with the name of the students.

• Go to C5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

Example 4 – Use the CONCAT Function for All Cells in a Column

Combine the cell values in column D.

• Go to B5 and enter the following formula.

• You will get the concatenated value of all cells in column D.

Example 5 – Using the CONCAT Function with a Separator

5.1. Space Separator

Find the full name using the CONCAT function with a space separator between first and last name.

• Go to D5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

5.2. Comma Separator

Combine student IDs, names, and departments with a comma separator.

• Go to E5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

5.3. Line Breaks

Use line breaks instead of commas to separate student ID, name, and department.

• Select E5:E9.
• Go to the Home tab >> Wrap Text in Alignment.

• Go to C5 and enter the following formula combining the CONCAT and CHAR functions.

• Drag down the Fill Handle to see the result in the rest of the cells.

Example 6 – Using the CONCAT Function with Dates

Join departments and the date when they released the results.

• Go to C5 and enter the following formula combining the CONCAT and TEXT functions.

• Drag down the Fill Handle to see the result in the rest of the cells.

Example 7 – Use the CONCAT Function in VBA

Use the CONCAT function in VBA to get the full name of the students.

• Go to the Developer tab >> Visual Basic.

• Select Insert >> Module.

• Enter this code in the VBA Macro Editor.
• Click Run or press F5 to run the code.

Sub CONCAT_Function()
    'variable declaration
    Dim WS As Worksheet
    Dim FirstName As Range
    Dim LastName As Range
    Dim FullName As Range
    'set variables
    Set WS = Sheets("VBA")
    Set FirstName = WS.Range("B5:B14")
    Set LastName = WS.Range("C5:C14")
    Set FullName = WS.Range("D5:D14")
    'use CONCAT function
    For i = 1 To FullName.Cells.Count
        FullName.Cells(i) = Application.WorksheetFunction. _
        Concat(FirstName.Cells(i), " ", LastName.Cells(i))
    Next i
    'display confirmation
    MsgBox "Full names have been added"
End Sub

You will see the full name of the students in D5:D14 of the VBA worksheet with a confirmation message.

For i = 1 To FullName.Cells.Count
        FullName.Cells(i) = Application.WorksheetFunction. _
        Concat(FirstName.Cells(i), " ", LastName.Cells(i))
    Next i

MsgBox "Full names have been added"

Alternatives to the CONCAT Function in Excel

1. Ampersand Operator

Use the ampersand operator (&) to get full names from the first and last names.

• Go to D5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

2. CONCATENATE Function

Use the CONCATENATE function to get the full names from the first and last names.

• Go to D5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

3. TEXTJOIN Function

Get student IDs, names and departments sequentially in a string using the TEXTJOIN function.

• Go to E5 and enter the following formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

Things to Remember

The CONCAT function does not provide options for delimiters of empty values.

The quotation mark may be displayed in the output if a comma isn’t added between arguments.

The CONCAT function does not recognize arrays. Enter cell references separately.

You can use numbers with or without the quotation mark.

The numbers become text values in the output.

Double quotes, asterisk (*) or forward slash (/) can be used to separate the concatenated strings.

The #NAME? error occurs when you miss a quotation mark in the text arguments.

The #VALUE? error occurs when the output string exceeds the maximum cell character limit (32767).

Frequently Asked Question

1. What is the difference between the CONCATENATE and CONCAT functions?

These two functions have the same purpose and format. The CONCATENATE function is available in all versions, whereas the CONCAT function is available in Office 2016 and later versions. The CONCAT function is concise, and can take a range of cells as input and return a text with all the elements of the range of cells. The CONCATENATE function can take a range of cells as input but does not return a concatenated text. It returns an array of cells.

2. Can I use the CONCAT function to combine data from multiple sheets?

Yes, by referencing the cell ranges from different sheets within the function.

3. What happens if I try to concatenate too many characters?

The resulting string may exceed the character limit for a cell (32767) and will show a #VALUE? error.

<< Go Back to Excel Functions | Learn Excel