How to Use CONCAT Function in Excel

In this article, we have talked about how to use the CONCAT function in Excel. We have demonstrated various examples of using the CONCAT function with texts, numbers, a range of cells, separators, and so on. Also, the CONCAT function can be used in VBA, and we have explored that. Lastly, we have talked about the alternatives to the CONCAT function in Excel.

The CONCAT function can be used to concatenate different values and range of cells. It can easily handle dynamic data from different sources. The CONCAT function is versatile, and it can handle data types such as text, numbers, and dates.

Download Practice Workbook

CONCAT Function.xlsm

Overview of CONCAT Function

The CONCAT function takes multiple arguments as input. The arguments can be of various data types. Then, it joins all of those arguments together and forms a string.

Syntax:

=CONCAT(Text1, …)

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 bear similar characteristics as Text1.

7 Suitable Examples to Use CONCAT Function in Excel

Here, we will discuss the use of Excel CONCAT function using the below examples.

1. Use CONCAT Function with Texts

We have multiple names in the dataset. We will add the domain name (@gmail.com) with these names to get the email addresses.

Go to cell C5 and put the following formula.

=CONCAT(B5,"@gmail.com")

Select cell C5 and use Fill Handle to AutoFill data in range C6:C14.

2. Use CONCAT Function with Numbers

We have several phone numbers here. We will add the country code (+1) before these numbers to get phone numbers with country code.

Go to cell C5 and put the following formula.

=CONCAT("+1",B5)

Select cell C5 and use Fill Handle to AutoFill data in range C6:C14.

3. Apply CONCAT Function for Range of Cells

We have the student IDs and name of some students in range B5:C14. We will use the CONCAT function with a range of cells to get the IDs along with the name of the students.

Go to cell D5 and put the following formula.

=CONCAT(B5:C5)

Select cell D5 and use Fill Handle to AutoFill data in range D6:D14.

4. Use CONCAT Function for All Cells in a Column

There are some random values in several cells of column D. We will combine these values together.

Go to cell B5 and put the following formula.

=CONCAT(D:D)

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

Note:

The CONCAT function skips empty cells. So, there will not be any gap in the resulting string of cell B5.

5. CONCAT Function with Separator

We can use separators like space, comma, and line breaks in the CONCAT function. We have to put them as arguments of the function, and they will be visible in the output.

5.1. Space Separator

We have the first names and last names of multiple people in range B5:C14. We will find the full name of those people using the CONCAT function with a space separator between first name and last name.

Go to cell D5 and put the following formula.

=CONCAT(B5," ",C5)

Select cell D5 and use Fill Handle to AutoFill data in range D6:D14.

5.2. Comma Separator

We have the student IDs, names, and departments of several students. We will get these values together with a comma-separated form.

Go to cell E5 and put the following formula.

=CONCAT(B5,",",C5,",",D5)

Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

5.3. Line Breaks

In this method we will use line breaks instead of commas to separate student ID, name, and department.

Select range E5:E9.
Go to the Home tab >> select Wrap Text from the Alignment group.

Go to cell E5 and put the following formula combining the CONCAT and CHAR functions.

=CONCAT(B5,CHAR(10),C5,CHAR(10),D5)

Select cell E5 and use Fill Handle to AutoFill data in range E6:E9.

Note:

Do not put a fixed height to the cells where the wrapped text will appear. If you do so, some of the text may be invisible and you will need to adjust the row height. Also, you may use the default row height or autofitted row height in these cells to solve the problem.

6. CONCAT Function with Dates

Here we have the departments and the date when they released their results. We will use the CONCAT function with these dates.

Go to cell D5 and put the following formula combining the CONCAT and TEXT functions.

=CONCAT(B5," published their result in ",TEXT(C5,"dd-mm-yyyy"))

Select cell D5 and use Fill Handle to AutoFill data in range D6:D9.

7. Use CONCAT Function in VBA

We have the first and last names of students like before. But this time we will use the CONCAT function in VBA to get the full name of the students.

Go to the Developer tab >> Visual Basic.

Select Insert >> Module.

Paste this code in your VBA Macro Editor.
Press the Run button or F5 key 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 range D5:D14 of the VBA worksheet with a confirmation message.

VBA Breakdown

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

This portion of the code uses a loop to iterate through the cells in the FullName range and concatenates the corresponding first name from FirstName range, a space, and the corresponding last name from LastName range. It uses the CONCAT function for concatenation. Then stores the resulting full name in each cell of the FullName range.

MsgBox "Full names have been added"

This line displays a MsgBox to indicate that full names have been added to the worksheet.

Alternatives to CONCAT Function in Excel

There are a few alternatives to the CONCAT function. The alternatives have been discussed in this section.

1. Ampersand Operator

We can use the ampersand operator (&) to get full names from the first and last names.

Go to cell D5 and put the following formula.

=B5 & " " & C5

Select cell D5 and use Fill Handle to AutoFill data in range D6:D14.

2. CONCATENATE Function

We can also use the CONCATENATE function to get the full names from the first and last names.

Go to cell D5 and put the following formula.

=CONCATENATE(B5," ",C5)

Select cell D5 and use Fill Handle to AutoFill data in range D6:D14.

3. TEXTJOIN Function

Here we have the student IDs, names and departments of several students. We will get these values sequentially in a string by using the TEXTJOIN function.

Go to cell E5 and put the following formula.

=TEXTJOIN(", ",TRUE,B5:D5)

Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

Things to Remember

There are a few things to remember while using the CONCAT function in Excel.

The CONCAT function does not provide any options for delimiters of empty values.
Sometimes the quotation mark may appear in the output. This happens if you forget to add a comma between arguments.
The CONCAT function does not recognize arrays. You have to enter the cell references separately.
You can use numbers with or without the quotation mark.
The numbers become text values in the output.
The CONCAT function can accept ranges in addition to individual cells.
Apart from double quotes, asterisk (*) or forward slash (/) signs can be used to separate the concatenated strings from other characters.
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 CONCATENATE and CONCAT functions?

These two functions have the same purpose and format. The difference is, the CONCATENATE function is available in all versions of Office 2007 through Office 365, while the CONCAT function is available in Office 2016 and later versions. The CONCAT function is concise, and it can take a range of cells as input and return a text with all the elements of the range of cells joined together. On the other hand, the CONCATENATE function does take a range of cells as input but does not return a concatenated text rather returns an array of cells as same as the input range in a column. So, these are the basic differences between the two functions.

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

Yes, you can use the CONCAT function to combine data from multiple sheets 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). In that case, it will show a #VALUE? error.

Conclusion

In this article, we have talked about how to use the CONCAT function in Excel with many useful examples. If you have any questions regarding this essay, don’t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!