How to Concatenate Two Texts into One Using Excel Formulas

One of the most popular text operations in Excel is the concatenation of text strings. For example, merging multiple words into one string. In practice, during routine work with Excel, it is often necessary to combine the Names and Nicknames of employees into a single cell.



The Best Alternative to the CONCATENATE Function for Text Concatenation in Excel

example of text merging

In the example shown in the image, the table contains the first two columns:

  1. Name - cells in this column contain text data obtained from the personnel records database.
  2. Nickname - this column contains only employee Nicknames.

In the third column "Name + Nickname," it is necessary to combine the Name and Nickname of each employee for subsequent use in Excel-generated pivot tables and reports.

merge name and nickname.

To achieve this, text values from cells in the table's columns need to be combined into common strings.

The illustrated example on the image shows a solution to this task. For this purpose, the "&" operator (used instead of the "+" operator) must be used in the example, allowing the concatenation of cell values in formulas:

example solution.

As shown above in the image, using the "&" symbol allows the concatenation of any text values from different cells without using the CONCATENATE() function. In this example, the values of cells A2 and B2 are combined with a space added between them (specified in the formula between quotation marks as a text value).

combine any text values.

To concatenate text into one string, Excel offers the built-in function
=CONCATENATE(A2," ",B2), which combines text values into one string. However, in practice, we do not recommend using the CONCATENATE function. Instead, simply apply the "&" operator in formulas, as it has several advantages over the CONCATENATE function.

3 main disadvantages of the CONCATENATE function compared to the "&" operator:

  1. Limited maximum number of concatenated cell values up to 255.
  2. The function concatenates only text values, while the "&" operator will concatenate any cell values.
  3. The function requires slightly more time and resources, both from the Excel program (during calculations using complex formulas) and from the user (during entering the function into the formula bar).

Interesting fact! In Excel, there is no reverse function for CONCATENATE, and there is no special symbol for the operator. To solve this problem, formulas with combinations of text functions are used. For example, the space character " " serves as a text marker for splitting the text, and then:

  • to split only the Name, use the formula: =LEFT(C2,FIND(" ",C2)-1)
  • to split only the Nickname – use the formula: =RIGHT(C4,FIND(" ",C4))
  • an alternative method to split text into separate values: =SUBSTITUTE(C5,A5&" ","")
SPLIT function.

Actually, the CONCATENATE function is outdated but still available to users in newer versions of the Excel program.

INDEX and MATCH Formula with Multiple Conditions

We will demonstrate the practical application of merging cell values with a ready-made example. Merging is very convenient to use in the arguments of the MATCH function when you need to perform a search with multiple conditions simultaneously across multiple ranges. For example:

=INDEX($C$2:$C$7,MATCH($E$2&$F$2,$A$2:$A$7&$B$2:$B$7,0))
formula for splitting text into fragments

Now, changing the first and/or second value will yield the corresponding result:

example of text merging

download file Download

To expand the capabilities of the INDEX and MATCH formula for multiple conditions, you can also add the VSTACK function. This allows for complex mergers of non-contiguous ranges. However, examples will be provided in future lessons.