## Remove duplicate text strings based on the 4 last characters

*Article updated on January 23, 2018*

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: **

*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: **

*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: **

*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:**

Copy cell D15 and paste it down to D17.

**Array Formula in E15:**

*+ 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

### One Response to “Remove duplicate text strings based on the 4 last characters”

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

Oscar,this works great. It saves me the trouble of creating a helper column. Countif cannot work directly with arrays.It looks like the match function created the array first,and countif worked on the result of this array. This would not work with COUNTIF(right(B2:B4,4),RIGHT(B2:B4,4)),4)

Thanks for your help. Keep up the great work.