How to filter values by first letter or last character in Excel?

When working with large datasets in Excel, you may sometimes need to filter values based on whether they start with a certain letter or end with a specific character. This can be useful, for example, when grouping customer names that begin with the same initial, or identifying entries with a given file extension or suffix. In this tutorial, we provide several practical solutions, each suited for different scenarios and user preferences. The solutions are suitable for users with varying Excel experience, from beginners relying on menu options to those comfortable with formulas or VBA.

Filter values by first letter or last character with Filter function
Filter values by first letter or last character with VBA code
Filter values by first letter or last character using Excel formula and helper column

Filter values by first letter or last character with Filter function

Excel’s built-in Filter function provides a straightforward way to filter data by the first letter or last character in a column. This method is best for simple, quick filtering needs, such as isolating all entries that start or end with a specific character, and does not require advanced Excel knowledge.

1. Select the entire column you want to filter for first or last character criteria. Then click Data > Filter in the Excel ribbon. A filter drop-down icon will appear at the top of the selected column. See screenshot:

2. Click the drop-down arrow in the column header to expand filtering options. Then navigate to Text Filters and choose either Begins With or Ends With, depending on your requirement. See screenshot:

3. In the relative dialog box, type your criteria:
(1.) To filter values by the first letter, select the Begins With option. In the Custom AutoFilter dialog that appears, enter the desired starting character (e.g., "a") in the box after "begins with" and click OK. For example, entering the letter “a” will immediately filter and show only those values beginning with "a".

(2.)To filter by the last character, choose Ends With in step2. In the Custom AutoFilter dialog box, enter the character you want as the filter criteria in the box after "ends with", and click OK. All values ending with this character will be displayed.

This is suitable for fast filtering, but may be limited for more complex, multi-condition filters or when custom logic is required. It's best applied when your data is primarily text and you need a quick, visual way to extract matching items. For more advanced or combined conditions, consider using formulas or helper columns described further below.

Filter values by first letter or last character with VBA code

For users who are comfortable using macros, VBA provides a more flexible way to filter data by first letter or last character—especially when dealing with large files, automating repetitive filters, or when specific logic not available through standard filters is needed. This method is effective when you need to apply the filter condition across multiple sheets, or design a repeatable process for frequent use.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.

2. In the VBA editor window, navigate to Insert > Module. In the new module window, paste the code provided below. Ensure that your sheet and range references match those of your dataset.

VBA code: Filter by first letter or last character

Sub filterbyletter()
	Dim rng01 As Range
	Set rng01 = [A1:A8]
	rng01.Parent.AutoFilterMode = False
	rng01.Columns(1).AutoFilter Field: = 1, Criteria1: = "=a*", VisibleDropDown: = False
End Sub

3. press F5 to run the code. The values matching the specified pattern will be filtered instantly.

If your data does not filter as expected, double-check that your range references and filter criteria are correctly specified. Always save a copy of your workbook before running macros, to prevent unintentional data changes. Also, ensure macros are enabled in your Excel settings.

Filter values by first letter or last character using Excel formula and helper column

This method uses a helper column with a logical formula to identify whether each value in your dataset starts or ends with a specific letter. You can then filter your data by the results (TRUE/FALSE) in this helper column. This approach is especially useful when:

• You have multiple, complex filter criteria
• You want to visually validate which items meet the criteria before filtering
• Built-in Filter options are insufficient (e.g., when combining AND/OR logic)
• You need to reuse, modify, or audit filtering logic later on

For example, if your data is in column A and you want to filter values that begin with the letter "a":

1. Enter the following formula into the adjacent empty column (e.g., in cell B2):

=LEFT(A2,1)="a"

2. After entering the formula, press Enter. If you want to apply this formula to all rows, use the fill handle to drag the formula down alongside your data range. 

3. Each cell in the helper column will now display either TRUE (if the value starts with "a") or FALSE. To filter, select your entire data range (including the helper column), then use Data > Filter. Apply a filter to the helper column to show only TRUE values, which represent your desired matches.

=RIGHT(A2,1)="a"

=AND(LEFT(A2,1)="a",RIGHT(A2,1)="z")