How To Concatenate In Excel | 3 Simple ways

When you are working with excel, there are times that you need to change the structure of the cells. This includes combining multiple columns, rows, or cells, which is called concatenation. When you concatenate in Excel, you join the content of various cells in one cell. In this blog, we are going to introduce different methods to concatenate content in Excel.

Excel CONCATENATE Function

The CONCATENATE function joins multiple items together as a text string in one cell. The function converts numbers to a text string and then joins them to the other strings. In the 2019 version of Excel, the CONCAT function replaced the CONCATENATE function. The syntax of the function is as bellows:

CONCATENATE(text1,[text2], …)

CONCAT(text1,[text2], …)

The text arguments are the numbers, text string, cell references, dates or formula-driven values that we want to join together.

Note: To insert a space, comma, or other characters between the strings, you must put them in double quotation marks (” “).

Things to remember about the CONCATENATE function

• The function needs at least one argument to work.
•  If you do not input a text string in a double quotation, the function will return the #NAME error.
• This function can concatenate up to 255 items, including a max of 8,192 characters. However, the CONCAT function can return up to 32,767 characters.

Unlock valuable insights with our Data Visualization and Data Analytics Services, transforming complex data into clear, actionable strategies for informed decision-making.

 

Excel TEXTJOIN Function

The TEXTJOIN function is another combining function in Excel. By using this function, you can include a delimiter between the texts. Numbers entered in the function will be converted to text strings during the concatenation. You can also ignore empty values. The syntax of the function is as below:

TEXTJOIN(delimiter,ignore_empty,text1,[text2], …)

Delimiter: This is a delimiter of your own choice (including space, comma, or a text string.)

Ignore_empty: This is a logical value. If TRUE, the function ignores empty cells.

Text arguments: The text strings or array of strings (such as range of cells) to be joined.

Note: The advantage of the TEXTJOIN function over the CONCAT or CONCATENATE function is that you can specify a delimiter, input range of cells, and ignore empty cells.

“&” Operator

Excel “&” operator works similar to the CONCATENATE function. Like the CONCATENATE  function, you can join space, comma, and other characters to the text string and cell values using double quotation marks.

Note: The difference between the “&” operator and the CONCATENATE function is that the “&” operator does not have the limit for the number of items you can concatenate.

Now let’s see two useful examples from concatenation in Excel.

Example 1: Concatenate with a line break

A line break in Excel is used to start a new line within a cell. To add a line break in Excel, you can press Alt + Enter, but when you want to concatenate with a line break, Alt + Enter will not work. In this case, you can use the CHAR function. If you are using Windows, CHAR(10) is the line break, and on Mac, it’s CHAR(13.)

Note: When using line break while concatenation, you need to enable the wrap text option.

Boost your productivity by getting a free consultation from Excel experts, and discover tailored solutions to optimize your data management and analysis.

Example 2: Concatenate Specific Formats

When you intend to concatenate a text string with a date, time, or other formats, you must give these formats to the function. To do so, you can use the TEXT function. Note that you must use the readable formats for the TEXT function. The syntax of the function is as below:

TEXT(value, format_text)

Where value is a number, date, or a cell reference that you want to convert to text and format_text is the format you want to present the value.

You can connect with us and ask our experts for your inquiries and get more Excel Support services.

Reduce cost, accelerate tasks, and improve quality with Excel Automation Services.