Search for the final 3 characters in a string of 6 in Excel/Google Sheets-CodePudding

I have a set of data within a spreadsheet that need to filter with specific parameters. I need to return data that matches the final 3 characters of a 6 character string.

For example if I have the following data:

| Col 1 |
---------
| AA-XYZ |
| AA-ABC |
| BB-XYZ |
| BB-DEF |
| CC-XYZ |
| CC-GHI |

My filter would return:

| Col 1 |
---------
| AA-XYZ |
| BB-XYZ |
| CC-XYZ |

Is this possible with a standard search/filter function or would a regular expression be required?

CodePudding user response：

=FILTER(A3:A8,RIGHT(A3:A8,3)="XYZ") or if you want to enter the filter in a cell =FILTER(A3:A8,RIGHT(A3:A8,3)=C1)

CodePudding user response：

Try below formula-

=FILTER(A1:A,INDEX(COUNTIFS(INDEX(SPLIT(A1:A,"-"),,2),INDEX(SPLIT(A1:A,"-"),,2)))>1)