How to use the Excel CONCATENATE function to concatenate cells

  1. Merge contents of two or more cells in Excel
  2. Understanding the Excel CONCATENATE Function
  3. CHORD Syntax and Arguments
  4. Combining Numeric Data
  5. How to use the Excel CONCATENATE function?
  6. Show ampere sign in concatenated text

Merge contents of two or more cells in Excel

In Microsoft Excel, concatenation generally refers to combining the contents of two or more cells in a worksheet into a third single cell using either one; This process can be done with the CONCATENATE function or the concatenation operator. See how to combine cells in Excel, which can improve your current workflow.

Understanding the Excel CONCATENATE Function

CHORD Syntax and Arguments

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.

Syntax for a function JOIN TOGETHER

 = CONCATENATE (Text1, Text2, ... Text255) 

Text 1 (required) can be real text, such as words or numbers, spaces in quotes, or cell references to find data on the worksheet.

Text2, Text3, … Text255 (optionally up to 255 text entries) can be added to the CONCATENATE function up to 8,192 characters long, including spaces. Each entry must be separated by a comma.

None of the concatenation methods automatically leave a space between words, which is good when joining two parts of a compound word like Baseball into one or concatenating two sets of numbers like 123456.

However, a space is required to concatenate the first name and last name or address, so it must be included in the concatenation formula, as shown in lines 4, 5, and 6 of the accompanying figure.

Combining Numeric Data

Even if the numbers can be combined, as shown in the sixth line of the sample image, the result is: 123456 is no longer treated as a number by the program, but is now treated as text data.

Received data in a cell C7 cannot be used as arguments to certain math functions such as SUM and AVERAGE. If such an item is included in the function arguments, it is treated as other text data and ignored.

An indication is that the merged data in the cell C7 are left aligned, which is the default alignment for text data. The same result occurs if the CONCATENATE function is used instead of the union operator.

How to use the Excel CONCATENATE function?

Although it is possible to enter the function completely in many people find it easier to use a dialog to enter function arguments because the dialog allows for entering parentheses, commas, and, in this case, quotes around a space, for example.

The steps below deal with entering a function with a dialog in a cell C2

  1. Click on a cell C2 to make it active.

  2. Click on the tab formulas

  3. Select Text functions on the ribbon to open the features drop-down list.

  4. Press JOIN TOGETHER in the list to open the function’s dialog box.

  5. Click on a line Text 1 in the dialog box.

  6. Click on a cell A4 on the worksheet to enter a reference to that cell in the dialog box.

  7. Click on a line Text 2 in the dialog box.

  8. Click Room on the keyboard to add a space to a string Text 2 † Excel adds double quotes around the space.

  9. Click on a line Text 3 in the dialog box.

  10. Click on a cell B4 on the sheet to enter a reference to that cell in the dialog box.

  11. Click Okay to close the dialog box and return to the worksheet.

consolidated name Mary Jones displayed in cell C4

When you click cell C4, the entire function appears in the formula bar above the worksheet.

 = CONCATENATE (A4, "", B4) 

Show ampere sign in concatenated text

In some cases, instead of the word And for example, the ampersand character is used in company names, as shown in the sixth line of the sample image.

To display an ampersand as a text character instead of using it as a concatenation operator, enclose it in double quotes like other text characters, as shown in the formula in the cell D6

Note that in this example, spaces are present on either side of the ampersand to separate that character from the words on either side. To achieve this result, enter spaces on both sides of the ampersand in double quotes, like this:

Likewise, if you are using a concatenation formula that uses an ampersand as the concatenation operator, you must also include spaces and the ampersand enclosed in quotes so that it appears as text in the formula results.

 = A6 & "&" & B6 

For example, the formula in cell D6 can be replaced with the above formula to achieve the same results.

Leave a Reply

Your email address will not be published. Required fields are marked *