Microsoft Excel allows you to concatenate text by using operators and functions. What does the word concatenate mean? Concatenation is the process of joining character strings end to end and has a wide variety of uses.
There are four different ways to concatenate text in Microsoft Excel each with its advantages and disadvantages:
- The & Operator
- Concatenation() – Deprecated replaced by the Concat() function
- Concat()
- Textjoin() – Available in Excel 2019 or if you have a Microsoft 365 subscription
The & Operator
The simplest way to concatenate text is by using the & Operator. The & operator works with individual cell references and also text strings. A negative is it will not allow you to ignore an empty field.
Syntax
=text1&text2&[text3]…
Example:
To combine the first name, middle initial, last name in the spreadsheet in the image above you would use the formula:
=A6&” “&B6&” “&C6
Note that if the middle initial is missing you will end up with a double space. There are ways to work around this but it makes the formula complex and messy.
Concatenation Function
The Concatenate function is simple. You basically list all the strings (up to 30) you want to combine separated by commas. The Concatenate has been replaced by the Concat function which is more flexible. It still works for compatibility’s sake. Unfortunately, it doesn’t handle delimiters so you still have to include the delimiter as a string in the function. It also doesn’t handle a range of cells so you have to enter them one by one.
Syntax:
Example:
To combine the first name, middle initial, last name in the spreadsheet in the image above you would use the formula:
=CONCATENATE(A6,” “,B6,” “,C6)
Concat Function
The Concat function replaces the Concatenation function. The Concat function’s major advantage is that you supply a range of cells to join, in addition to individual cell references. The Concat function doesn’t allow you to use delimeters. So if you need to have a delimiter between individual text items you have to add it manually as a string.
Example:
You could use a range to combine the first name, middle initial, last name by using the formula:
=CONCAT(A6:C6)
Unfortunately, this would put the three text items together without a space between them.
To combine the first name, middle initial, last name with a space between them (a delimiter) you would have to use the formula:
=CONCAT(A6,” “,B6,” “,C6)
Textjoin Function
The fourth function, Textjoin is the most powerful way to concatenate text.
Textjoin feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
The Textjoin function allows you to use delimiters, allows you to exclude include empty fields, and also allows you to use ranges.
- delimiter – Separator between each text.
- ignore_empty – Whether to ignore empty cells or not.
- text1 – First text value or range.
- text2 – [optional] Second text value or range.
Example
To combine the first name, middle initial, last name with a space between each and to ignore the middle initial if empty you would use the formula:
=TEXTJOIN(” “,TRUE,A6:C6)
- The first parameter is the delimiter you want to use – in this case a space
- The second parameter is a Boolean Variable that tells Excel if you want to exclude empty fields.
- The last set of parameters are the strings that you want to concatenate. Note that you can use a range.
Download Exercise File for Text Concatenation Exercise: Download
Check out Microassist Classroom and Virtual Training
Subscribe to Monthly Training Updates
Receive monthly productivity and training insights, software tips, and notices of upcoming classes!