## Concatenate cell values based on a condition [No VBA]

*Article updated on February 26, 2018*

The following image shows you data in column B and C. I want to concatenate adjacent value to size "M" into cell F4. They are "CD", "IJ" and "OP", in picture below.

I highly recommend using the TEXTJOIN function if you own Excel 2016. It is what the CONCATENATE function should have been from the beginning:

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

### Instructions

- Copy (CTRL + c) this formula:

=TRANSPOSE(IF(B3:B11=F3,C3:C11,"")) - Double click on cell F4
- Paste (Ctrl + v) formula to cell F4
- Select the entire formula in the formula bar

- Press Function key F9 and the formula is converted to an array of constants:

={"","CD","","","IJ","","","OP",""} - Delete the equal sign = in the formula bar and then press Enter

{"","CD","","","IJ","","","OP",""} - Select cell F4
- I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box

- Type in Find what: "", and nothing in Replace with:
- Click on "Replace" button

- Type in Find what: ,"" and nothing in Replace with:
- Click "Replace" button once again
- Click Close button

### Add all values in array

The final thing to do is to use the concatenate function to add all values into one text string.

- Double click on cell F4
- Delete the curly brackets from the formula: {}
- Add this: =CONCATENATE(
- and then an ending parentheses )

- Press Enter

### Add delimiting character

Perhaps you want a delimiting character between values, right after you have converted the formula to an array of constants (step 5, above), do this:

- The array looks like this: ={"","CD","","","IJ","","","OP",""}
- Add an ampersand and then a delimiting character with quotation marks.

={"","CD","","","IJ","","","OP",""}&"|" - Select the formula and press function key F9, the formula now looks like this:

={"|","CD|","|","|","IJ|","|","|","OP|","|"}

The ampersand has added the delimiting character to all values in the array. - Continue with step 6 above.
**Tip!**To delete empty values in array, Search and Replace with these values "|", and ,"|"

### User defined function

If you think the above instructions are too tedious and want something quicker and easier, check out this UDF. You can find it near the bottom of this article:

How to use the TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

### Download excel *.xlsx file

Concatenate a cell range using a condition.xlsx

Recommended article

Quickly concatenate values into one cell [No VBA]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Use a drop down list to extract and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article