Concatenate Strings in Excel: A Comprehensive Guide

Concatenate Strings In Excel

Concatenation is a fundamental operation in Excel. This one basically involves combining two or more text strings into one. It is an important function. And it is widely used in data analysis, reporting and other applications. In this article, we will explore various methods to concatenate strings in Excel. Such as along with tips and tricks and this will help you get the most out of it.

  1. Using concatenate two strings in excel

The CONCATENATE function is the most basic method of combining text strings in Excel. It is easy to use and works with any version of Excel. In case you wanna use the CONCATENATE function, you should simply follow these steps.

  • Now, you can enter the formula =CONCATENATE(string1,string2,…) in a cell to see concatenated text. You can then replace string1, string2 and so on with the text strings you want to combine.
  • You can press Enter to display the concatenated text.

For example, in case you wish to concatenate the text strings Hello and World into Hello World. You then enter the formula =CONCATENATE(“Hello”, “World”) in a cell and basically press Enter.



  1. Using the “&” operator for concatenating strings in excel

Another way is using the “&” operator. This method is more concise and easier to read than the CONCATENATE function. In order to use the “&” operator, follow these steps:

  • As a first step, you can enter the first text string in a cell. This should followed by the “&” operator and the second text string. And so on. For example, you an write down “Hello” & “World” to display “Hello World”.
  • You can press Enter to display the concatenated text.
  1. Using the TEXTJOIN function to concatenate function in excel

The TEXTJOIN function is a newer function in Excel from Excel 2016. It is a more versatile and better in concatenating text strings with a delimiter. In case you wish to use the TEXTJOIN function, you can follow these steps.

  • Now, first you will enter the formula =TEXTJOIN(delimiter, ignore_empty, string1, string2,…) in a cell. After that you will Replace delimiter with the separator you want to use between the text strings. You can also use TRUE for ignore_empty to exclude empty cells from this operation. Basically now you will replace string1, string2 and all other text strings you want to combine.
  • Press Enter to display the concatenated text.

As an example, you can write the text strings “Hello”, “World”, and “!” into “Hello World!”. And then you can enter the formula =TEXTJOIN(” “, TRUE, “Hello”, “World”, “!”) in a cell and press Enter.

  1. Using the CONCAT function for string concatenation in excel

The CONCAT function is another newer function since 2019. It is similar to the CONCATENATE function but with a simpler syntax. T

  • The formula is =CONCAT(string1,string2,…). And then, you will replace string1, string2 and all text strings you want to combine.
  • Now you can Enter to display the concatenated text.

In order to concatenate the text strings “Hello” and “World” into “Hello World”, you will enter the formula =CONCAT(“Hello”, “World”) in a cell and press Enter.

  1. Using the JOIN function for concatenate string in excel

The JOIN function also can concatenate text strings with a delimiter. It works by combining an array of text strings into a single string with a specified separator.

  • If you wanna try join function, then you will enter the formula =JOIN(delimiter, array) in a cell. Then you can replace delimiter with the separator you want to use between the text strings.
  • It is as simple as to press Enter for concatenated text.

If you wish to concatenate the text strings “Hello”, “World”, and “!” into “Hello World!”, then the join formula will be this: =JOIN(” “, {“Hello”, “World”, “!”}) in a cell and press Enter.

  1. how to concatenate string in excel with functions

In addition to the methods mentioned above, you can also concatenate text strings with various functions in Excel. For example, you can use the LEFT function to extract the leftmost characters of a text string. And after that, you can concatenate them with other text strings. But you can also use the RIGHT function to extract the rightmost characters of a text string. After that, you will concatenate them with other text strings.

  1. Tips and tricks on how to concatenate a string in excel



Here are some tips and tricks for concatenation in Excel.

Concatenate Strings in Excel

  1. Basic version of all is to use the CONCATENATE function or the “&” operator for simple concatenation tasks.
  2. Also, you can use the TEXTJOIN or JOIN functions for more complex ones such as delimiters or arrays.
  3. As a bonus method, you can use functions like LEFT and RIGHT to extract characters from text strings before concatenating takes place.
  4. Now with new Excel versions, you can use the CONCAT function for a simpler syntax.
  5. You can be mindful of empty cells when concatenating with the TEXTJOIN tough. Also, it is better to use TRUE for ignore_empty to exclude empty cells from the concatenation.
  6. As always, you should use TEXT function to convert numbers to text before concatenating them.

how to concatenate string and cell value in excel

In conclusion, concatenation is a powerful function in Excel to combine text strings. There are multiple methods to do combinations for text strings in Excel. And most popular ones are including the CONCATENATE function, the “&” operator, the TEXTJOIN function, the CONCAT function and the JOIN function. Hence, you can use any of these methods for your needs and get better at organizing sheets.

Related posts


Leave a Comment