CONCATENATE function and Opposite of concatenate in Excel

The function CONCATENATE is used to combine values (textual and numerical) from different cells into one. Its analog is & (ampersand). The sign copes with the simplest tasks. But it is not suitable to combine a lot of rows.



Syntax of the concatenate function

The function is part of the "text" group. Syntax: (txt1; txt2; ...). You can include up to 255 text strings. The first argument "text 1" is mandatory. Any arguments can be specified as text, numbers or cell references.

General rules for using the CONCATENATE function:

  1. Use a semicolon to separate arguments. Skipping or using other characters will cause an error to be displayed.
  2. The text arguments must be enclosed in quotes. Otherwise, an error appears in the form of the inscription # NAME? Numbers and cell references are not necessary.
  3. You need to add spaces to the formula with the function in order for the united elements in the result line to be displayed with "space". Using double quotes with a space inside (" ") or with "space" after the text argument ("Ivanov "; ...).


How to use the concatenate function in Excel?

Let's begin with the simplest examples. Data to be merged:

simplest examples.

We put the cursor in cell E2 and call the formula wizard (button fx). In the category "Text" we find the name CONCATENATE. Alternatively, select: «FORMULAS»-«Text»-«CONCATENATE». Arguments are references to cells A2, B2, C2 and spaces:

CONCATENATE.

The result of combining values in cells:

combining values.

The same result is obtained with the help of an ampersand:

ampersand.

One of the arguments is text. Formula: Result:

Formula.

The CONCATENATE function in Excel will do more complex tasks. Examples:

  1. Connect the text with the date. The data is in separate cells. The "Date" column is set to "Date" format. The date will be displayed in numerical format if you use only the CONCATENATE function. Therefore, we add the function TEXT to format the numbers. Formula: The second argument of the TEXT function is the date format. Result:
  2. TEXT.
  3. We connect many cells in a row at once. It will be long and inefficient if you write a formula with the CONCATENATE function. We use a little trick. Here is our line with the values in the separate cells. We put the sign "equal" in cell H1. Select the range with the values A1: J1. Enter ampersand & and space " ". Push F9 button. The array formula appears in the formula bar. We put the cursor in the formula line after the "equal" sign. Write word CONCATENATE. Curly brackets are replaced by round brackets and then press ENTER. We get the desired result.
  4. desired result.
  5. We connect values using slash ("-", "&", etc.). The formula will look like this: = CONCATENATE( a reference to cell 1; "/"; reference to cell 2).
slash.

Opposite of CONCATENATE in Excel

There is no standard function that would separate the values of one cell. You can use other functions to perform a number of similar tasks:

  • LEFT (displays the specified part of the characters from the beginning of the line);
  • RIGHT (displays the specified part of the characters from the end of the line);
  • MID (displays the specified part of the characters starting at the specified position) and others.

Examples Inverse CONCATENATE function:

  1. We extract the first word from the string by applying the LEFT function - first opposite of concatenation in Excel. Arguments are "text" ( a reference to the string with the desired value), "quantity of characters" (the character quantity is to be extracted).
  2. LEFT.
  3. Let's extract the last word from the line using the RIGHT function. The syntax is similar (as in the previous example). Only the quantity of characters is counted from the end.
  4. RIGHT.
  5. The main opposite of concatenation in Excel is MID. We extract a surname (the second word) from the entry in the line with the help of the MID function. Arguments are "text" ( a reference to a string), "initial position" (the symbol number from which the program will start counting the character quantity; " quantity of signs". In the example: E2 - "text", after the 9th symbol, the formula will return 8 characters.
MID.

More complex formulas are used and user functions are compiled to perform more complex tasks.


en ru