+3 votes
7 views
in Office by (237k points)
reopened
Excel: Use the CONCATENATE function correctly

1 Answer

+4 votes
by (1.6m points)
 
Best answer

CONCATENATE syntax and arguments
Use the CHAIN ​​function
1st step:
2nd step:
3rd step:
4th step:
quick start Guide

Combine the contents of two or more cells in Excel with the CONCATENATE function..

image image

Concatenation in Excel combines the contents of two or more cells in a worksheet into a third, separate cell. This process is carried out either with the CHAIN ​​function or with the chaining operator.

Read on here for tips on how to work efficiently with Excel.

CONCATENATE syntax and arguments

The syntax relates to the layout of the function and includes the function name, parentheses, comma separators, and arguments..

The syntax for the CONCATENATE function is: = CONCATENATE (Text1; Text2, ...)

  • Text1 (required): The first element to be combined. It can be text, such as words or numbers, quoted spaces, or cell references indicating the location of data on a worksheet.
  • Text2; Text3; ...; Text255 (optionally up to 255 text entries): The other entries to be combined. These can be added to the CONCATENATE function up to a maximum of 8,192 characters including spaces. Each entry must be separated by a semicolon.
image

Note on concatenating numbers: Even if numbers can be concatenated, as can be seen in line seven of the example image, Excel does not regard the result 123456 as a number, but as text. Therefore, the result shown in cell C7 cannot be used as an argument to certain math functions such as SUM and AVERAGE. When this type of input is included in the arguments of a function, it is treated like other text data and is ignored.

Use the CHAIN ​​function

Although it is possible to enter the entire function manually, it may be easier to use the dialog box to enter the arguments of a function. The dialog box takes care of entering brackets, commas, and, in this example, the quotation marks that surround the space. Follow our step-by-step instructions or take a look at the brief instructions ..

1st step:

image
Select a desired cell in which the result of the function should be. Then switch to the " Formulas " tab at the top and click the " Text " button . Then select the option " Insert function ... " from the menu .

2nd step:

image
Now enter the keyword Concatenate in the text field " Search function " and click on " OK " at the top right . Then select the CHAIN ​​function and confirm with " OK ".

3rd step:

image
Now enter the required cells and separators in the " Text ... " text fields . You can also just click the cells in your spreadsheet in Excel instead of entering the text manually. If desired, you can also enter spaces in the text fields to separate the contents of the cells with a space. Excel automatically inserts double quotation marks around the space. Then click " OK " to complete the function.

4th step:

image
The result of the formula is now in the cell you selected previously.

quick start Guide

  1. Select the cell in which the result of the function should be and click on " Text " in the " Formulas " tab .
  2. Then click on " Insert function ... " in the menu .
  3. In " Search for a function ", enter Concatenation and then search for the function with " OK " at the top right.
  4. Then select the CHAIN ​​function and click OK at the bottom .
  5. Now enter the required cells and separators in the " Text ... " text fields . Then click OK .
  6. The result of the formula is now in the cell you selected previously.

...