## How to use the CONCAT function

**What is the CONCAT function?**

The CONCAT function concatenates values from multiple cells, cell ranges and arrays.

**What is concatenate?**

This means that Excel adds the values in a series forming a text string.

**What is an array?**

An array in Excel is a collection of values in rows and columns, enclosed in curly brackets {}. The values are separated by a delimiter for rows and another for columns.

For example, this array formula spills the values into individual cells:

={"Car", "Bike";"Train",5}

In Excel 365, arrays automatically spill results into adjacent cells as needed. But in older versions, you must enter it as an array formula (Ctrl + Shift + Enter) to view each value in a cell.

**What is a cell range?**

A cell range is basically multiple adjacent cells in worksheet. You reference a cell range using the column and row values like this: =A3:B5 so the CONCAT function becomes CONCAT(*A3:B5*)

**What is a delimiter?**

It is basically a string that separates the output values, however the CONCAT function lacks this feature. I recommend you use the TEXTJOIN function.

#### Table of Contents

## 1. CONCAT function Syntax

CONCAT(*text1*, *[text2]*,â€¦)

## 2. CONCAT function Arguments

text1 |
Required. Values you want to combine. |

[text2] |
Optional. Up to 254 additional arguments. |

## 3. CONCAT function example

## 4. CONCAT function not working

The CONCAT function returns an error if the source range contains an error.

The #NAME! error is shown if you misspelled the function.

## 5. How to add delimiting characters to the CONCAT function

Formula in cell D3:

Use the following formula to remove the last delimiting string:

### Explaining formula

#### Step 1 - Join each cell value in range with a delimiting string

B3:B6&", "

#### Step 2 - Concatenate array

CONCAT(B3:B6&", ")

## 6. Comparing related functions

**The ampersand characterÂ &** lets you concatenate values in a formula. Ampersand

- No advanced options.
- Easy to use.

**The CONCATENATE function** is a simple function that allows you to quickly join values.Â Â CONCATENATE

- Has been replaced by the CONCAT function.
- Although the CONCATENATE function still exists in Excel for backward compatibility, it is a legacy function and may not be supported in future releases.
- You need to select each cell one by one which may become tedious and time consuming.
- Hold SHIFT key while selecting cells to avoid typing delimiting characters between arguments.

**The CONCAT function** is a simple function that allows you to quickly join values from a cell range. CONCAT

- No delimiting value.
- CONCAT replaces the CONCATENATE function, Microsoft recommends you use this function over the CONCATENATE function from now on.

**The TEXTJOIN function**Â is more advanced, it lets you specify a delimiting value and ignore blank values. It takes multiple non adjacent cell ranges.Â TEXTJOIN

- The TEXTJOIN function is likely the most versatile option for concatenating text across multiple cells and ranges in Excel.
- You can specify delimiting values, however, no distinction between row and column delimiting values which is the case of the ARRAYTOTEXT function.
- You have the option to ignore blank values.

**ARRAYTOTEXT function** concatenates values from a given cell range or array. ARRAYTOTEXT

- Allows you to specify delimiters for both columns and rows.
- The result is a text string.

**Function key F9** lets you convert the formula to the output result.

- Hard code the values in a formula.
- You have the option to select a part of the formula.
- Press Escape key to undo changes.

Here is how:

- Select the cell containing the formula you want to convert. The formula may be as simple as this: =B2:D5 which is a cell reference to cell range B2:D5.
- Press with left mouse button on in the formula bar so the prompt appears.
- Select the entire formula.
- Press F9 on your keyboard. Excel converts the formula and now shows the output from the formula.
- Press Esc key to go back to the original formula or press Enter to keep the changes.

## 7. CONCAT function and date and time values

Formula in cell D6:

### Explaining formula in cell D6

#### Step 1 - Format time values

TEXT(B3:B6,"hh:mm AM/PM ")

#### Step 2 - Concatenate formatted time values

CONCAT(TEXT(B3:B6,"hh:mm AM/PM "))

### Useful resources

CONCAT function - Microsoft support

### 'CONCAT' function examples

The following article has a formula that contains the CONCAT function.

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

### Functions in 'Text' category

The CONCAT function function is one of many functions in the 'Text' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form