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

*Article last 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 […]

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 […]

Concatenate unique distinct values

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]

Arielle asks: i have a formula in row a from A1:Z1 that displays "" if there is an error. I […]

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 comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form