Sean asks:

The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on the last 4 characters in a cell. This would mean using the right function.
1845-CA-2176
1845-CO-2145
1846-CA-2145

The unique list here is 2145 and 2176. Is there a short but sweet formula like the original countif formula above?

and

The following formula will not work with countif- =TEXT(A1:A3,mmmmyy).

Answer:

To better understand how the array formula works, we start with the easier text functions.

Formula in D2:

=LEFT(B2,4) + ENTER.

Copy cell D2 and paste it down to D4.

LEFT(text, num_chars) Returns the specified number of characters from the start of textstring

Formula in E2:

=MID(B2,6,2) + ENTER.

Copy cell E2 and paste it down to E4.

MID(text, start_num, num_chars)
Returns the characters in the middle of a text string, given a starting postion and length

Formula in F2:

=RIGHT(B2,4) + ENTER.

Copy cell F2 and paste it down to F4.

RIGHT(text, num_chars) Returns the specified number of characters from the end of textstring

Array Formula in D8:

=LEFT(INDEX($B$8:$B$10, MATCH(0, COUNTIF($D$7:D7, LEFT($B$8:$B$10, 4)), 0)), 4) + CTRL + SHIFT + ENTER.

Copy cell D8 and paste it down to D10.

Array Formula in E8:

=MID(INDEX($B$8:$B$10, MATCH(0, COUNTIF($E$7:E7, MID($B$8:$B$10, 6, 2)), 0)), 6, 2) + CTRL + SHIFT + ENTER.

Copy cell E8 and paste it down to E10.

Array Formula in F8:

=RIGHT(INDEX($B$8:$B$10, MATCH(0, COUNTIF($F$7:F7, RIGHT($B$8:$B$10, 4)), 0)), 4) + CTRL + SHIFT + ENTER.

Copy cell F8 and paste it down to F10.

Formula in D15:

=TEXT(B15, "mmmmyy") + ENTER.

Copy cell D15 and paste it down to D17.

Array Formula in E15:

=TEXT(INDEX($B$15:$B$17, MATCH(0, COUNTIF($E$14:E14, TEXT($B$15:$B$17, "mmmmyy")), 0)), "mmmmyy") + CTRL + SHIFT + ENTER.

Copy cell E15 and paste it down to E17.

Download excel sample file for this tutorial.

Filter unique distinct strings within a cell.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

LEFT(text, num_chars) Returns the specified number of characters from the start of textstring

MID(text, start_num, num_chars)
Returns the characters in the middle of a text string, given a starting postion and length

RIGHT(text, num_chars) Returns the specified number of characters from the end of textstring

TEXT(value, format_text)
Converts a value to text in a specific number format