## Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

*Article updated on February 19, 2018*

Table of contents

Filter unique distinct values using a "contain" condition

Filter unique distinct values using a "contain" condition (Advanced filter)

Filter unique distinct values beginning with a letter

### Filter unique distinct values using "contain" condition of a column in excel (array formula)

Array formula in C2:

copied down as far as needed.

**Named ranges**

List (A2:A20)

What is named ranges?

### Download excel sample file for this tutorial

Filter unique distinct values using contain condition from a column.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

**NOT(**logical**)**

Changes FALSE to TRUE, or TRUE to FALSE

### Filter unique distinct values using "contain" condition of a column in excel (Advanced filter)

- Select the range (A1:A20)
- Click "Data" tab on the ribbon
- Click "Advanced" on the ribbon

- Click "Copy to another location"
- Select A1:A20 in "List range:"
- Click C1 in "Copy to:"
- Click "Unique records only"
- Click OK!

- Select C2
- Click "Filter" on the ribbon
- Click "Black triangle" in cell C1

- Click "Text Filters"
- Click "Contains..."

- Type "r" in "contains" window
- Click ok!

### Download excel sample file for this tutorial

Select Sheet "Adv. Filter

Filter unique distinct values using contain condition from a column.xls

(Excel 97-2003 Workbook *.xls)

### Filter unique distinct values beginning with a letter

**Array formula in cell C3:**

**How to create an array formula**

- Select cell C3
- Click in formula bar
- Copy and paste formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

**How to copy array formula**

- Select cell C3
- Copy cell C3 (Ctrl + c)
- Select cell range C3:C6
- Paste (Ctrl + v)

**Download excel *.xlsx file**

extract unique distinct values beginning with a letter.xlsx

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]### 6 Responses to “Filter unique distinct values if value contains specific string [Formula and Advanced Filter]”

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

I have a data in same way only difference is that they are in number format i.e. (330-1541) in this way so is there any way i can use the filter for such type of data. Please help me.

Ashok,

did you create an array formula?

[...] For Extracting Records From Data Set (12 Examples) - YouTube Bill Jelen - YouTube Or here... Filter unique distinct values using “contain” condition of a column in excel | Get Digit... I hope this helps. [...]

Do you have examples for using advanced filter for "Not Contains"?

I have a data of this sort...

Probe ID Call ID

USBE1 130226200131-1

USBE1 130226200131-2

USBE1 130226200131-3

USBE1 130226200131-4

USBE1 130226200521-1

USBE1R1 130227143154-1

USBE1R1 130227143154-10

USBE1R1 130227143154-11

USBE1R1 130227143154-12

USBE1R1 130227143154-13

USBE1R1 130227143154-14

USBE1R1 130227143154-15

..The "" condition works on the first column, but not on the second column..I am treating both as text columns. But the advanced filter for "Not" condition on these are not working..any help?

dashil103,

Sorry I don´t understand. What is the desired output?

I would like to do something vaguely similar to this. I would like to filter a list with tho columns. In the first column is an ID ("List 1", "List 2", "List 3", and so on), in the second one is a value. Now i would like to list all the values where the first row contains "List 1". Could you help me with this?