# How to use the CONCAT function

**What is the CONCAT function?**

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

#### Table of Contents

## 1. Introduction

**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.

## 2. Syntax

CONCAT(*text1*, *[text2]*,…)

text1 |
Required. Values you want to combine. |

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

## 3. Example

This image shows a portion of an Excel spreadsheet. Column B (labeled "Values") contains the following entries:

B3: AA B4: FQ B5: NM B6: 34

Cell D2 contains the text "CONCAT function". The formula bar at the top of the image shows the formula in the currently selected cell (D3).

Formula in cell D3:

and displays the result "AAFQNM34".

The CONCAT function is used to combine text from multiple cells or ranges into one text string. Input values:B3:B6 is the range of cells being concatenated.

The result "AAFQNM34" is displayed in cell D3. This is the concatenation of all the values in the range B3:B6, joined together without any separators.

The CONCAT function simply joins all these values in the order they appear in the range, resulting in the combined string "AAFQNM34".

## 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:

This Excel formula is used to concatenate a range of cells (B3:B6) into a single string separated by commas, then remove the trailing comma and space at the end.

Here's a breakdown of how it works:

**CONCAT(B3:B6&", ")**: This part of the formula concatenates the values in cells B3:B6 into a single string, with each value followed by a comma and a space. The & symbol is used to concatenate the values with the comma and space.**LEN(CONCAT(B3:B6&", "))**: This part of the formula calculates the length of the concatenated string.**LEFT(CONCAT(B3:B6&", "),LEN(CONCAT(B3:B6&", "))-2)**: This part of the formula uses the LEFT function to extract a substring from the concatenated string, starting from the left. The length of the substring is calculated by subtracting 2 from the total length of the concatenated string. This effectively removes the trailing comma and space.

### 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

This Excel formula is used to concatenate a range of time values in cells B3:B6 into a single string, with each time value formatted as "hh:mm AM/PM".

Formula in cell D6:

Here's a breakdown of how it works:

**TEXT(B3:B6,"hh:mm AM/PM ")**: This part of the formula formats each time value in cells B3:B6 as a string in the format "hh:mm AM/PM". The TEXT function is used to convert the time values to strings, and the format code "hh:mm AM/PM" specifies the desired format.- hh formats the hour as a 12-hour clock (01-12)
- mm formats the minute as a zero-padded two-digit value (00-59)
- AM/PM formats the time as either "AM" or "PM" depending on the time of day

**CONCAT(...)**: This part of the formula concatenates the formatted time strings into a single string.

For example, if cells B3:B6 contain the time values 08:00, 12:00, 15:30, and 20:45, the formula would return the string "08:00 AM 12:00 PM 03:30 PM 08:45 PM".

### 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

What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

### Functions in 'Text' category

The CONCAT function function is one of 29 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