Description
Use this function to join together text strings from multiple cells based on a condition.
CONCATENATEIF is useful for merging data, generating summaries, and creating custom text outputs based on criteria.
Syntax
CONCATENATEIF(range, criteria, [delimiter], [concatenate_range])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
search_range |
Yes | The range of cells to be checked for the value in criteria. |
A cell reference, a cell range, or a formula which results in either of these. |
criteria |
Yes | The value that each cell in search_range must meet to be included in the concatenation. |
A text string, number, or a formula which results in either of these. |
delimiter |
Yes | The value to insert between concatenated values. | A text string, such as a comma, space, or any other supported character. |
concatenate_range |
No | The range whose cells match the rows that match criteria. |
A cell reference, a cell range, or a formula which results in either of these. |
Example
Sample data
| A | B |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Cherry |
| 4 | Date |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=CONCATENATEIF(B1:B5, "Apple", ", ") |
Concatenates values in the range B1:B5 that contain the word "Apple", separated by a comma and space. | Apple |
=CONCATENATEIF(B1:B5, "*a*", " | ") |
Concatenates values in range B1:B5 that contain the letter "a", and returns them separated by a pipe. | Apple | Banana | Date | Apple Pie |
=CONCATENATEIF(B1:B5, "Date", "|") |
Concatenates values in range B1:B5 that contain the word "Date". | Date |
=CONCATENATEIF(B1:B5, "Date", "|", A1:A5) |
Concatenates values in range B1:B5 that contain the word "Date", and returns the equivalent value from the range A1:A5. | 4 |
=CONCATENATEIF(B1:B5, "*a*", " | ", A1:A5) |
Concatenates values in range B1:B5 that contain the letter "a", and returns the equivalent value from the range A1:A5 separated by a pipe. | 1 | 2 | 4 | 5 |
Notes
- If no cells meet the criteria, CONCATENATEIF returns an empty string.
- The delimiter is optional; if omitted, the values are concatenated without any separator.
- The range specified in
concatenate_rangemust be at least the size of the range supplied insearch_range. - CONCATENATEIF supports wildcards in the criteria, such as * for any number of characters and ? for a single character.
- CONCATENATEIF is case-insensitive.
Tips
- CONCATENATEIF can be combined with other functions such as IF and TEXT to create dynamic and flexible text outputs.
- Consider using CONCATENATEIF with large data sets to aggregate and analyze specific subsets of information.
- CONCATENATEIF is particularly useful for creating lists or strings from Sample Data that meet specific conditions.
Related functions
CHAR
CODE
CONCATENATE
CONCATENATEIF
FIND
LEFT
LEN
LOWER
MID
PROPER
REPT
RIGHT
SEARCH
SUBSTITUTE
TEXT
TEXTJOIN
TRIM
UNICHAR
UNICODE
UPPER