Remove duplicate text strings based on the 4 last characters in a cell in excel
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:
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:
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:
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:
Copy cell D8 and paste it down to D10.
Array Formula in E8:
Copy cell E8 and paste it down to E10.
Array Formula in F8:
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:
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
Related posts:
Filter duplicate text values in a range using “begins with” criterion in excel
Create unique distinct list sorted based on text length using array formula in excel
Text to columns: Split words in a cell (excel array formula)
Extract duplicate text values from a range containing both numerical and text values in excel
Recommended categories
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.