How to check if cell begins or ends with a specific character in Excel?

When managing data in Excel, you may encounter situations where you need to verify whether certain cells start or end with a specific character. For example, you might want to identify all product codes starting with "s" or all email addresses ending with "n". This type of check is common in data cleaning, list filtering, and for setting up automatic formatting or selection rules. Depending on the complexity of the task and your workflow preferences, there are several practical ways to achieve this.

Check if cell begins or ends with a specific character with formula
Check if cell begins or ends with a specific character with Kutools for Excel
Visually highlight cells if they begin or end with a specific character using Conditional Formatting
Check if cell begins or ends with a specific character using VBA code

Check if cell begins or ends with a specific character with formula

Suppose you have a long list of values and need to quickly determine whether each one starts or ends with a particular character. Excel formulas provide a direct, flexible approach for this need without the use of additional tools or add-ins. This solution is widely adaptable for large data sets, especially when an immediate, column-based YES/NO check is required for further analysis, reporting, or validation rules.

For example, you want to check if a cell begins with the character “s”, or ends with the character “n”. Here are formulas you can use:

1. Select a blank cell (such as B2) next to your data for showing the check result, enter the following formula in the Formula Bar, then press the Enter key:

=IF(LEFT(TRIM(A2),1)="s","OK","Not OK")

The formula trims leading/trailing spaces from A2 and checks if the first character is "s". If it matches, it returns OK; otherwise, it returns Not OK.

2. Keep selecting cell B2, then drag the Fill Handle down to copy the formula for the rest of your list. This will allow you to check every relevant cell in your chosen range.

Notes and Tips:

=IF(RIGHT(TRIM(A2),1)="n","OK","Not OK")

Generally, using formulas is quick and easy when you want results directly in cells for sorting, filtering, or further calculations.

Check if cell begins or ends with a specific character with Kutools for Excel

The Select Specific Cells tool in Kutools for Excel provides an intuitive, efficient way to identify and select cells that begin or end with specific characters. This approach is especially useful when you want to visually select, highlight, or further operate on cells meeting your criteria (such as batch deleting, formatting, or copying), rather than just displaying a result in an adjacent column.

How to Use:

1. Begin by selecting the range where you need to check for beginning or ending characters. Then, click Kutools > Select > Select Specific Cells. See screenshot:

2. In the Select Specific Cells dialog box, select Cell in the Selection type area. In the Specific type dropdown, choose Begins with or Ends with, and enter the character you wish to check. Click OK.

3. A dialog box will appear summarizing the number of matches found. Confirm to see the selected cells highlighted.

Note: This tool allows you to specify up to two criteria. For instance, you can select cells that either begin with "F" or end with "s" simultaneously, streamlining multi-condition checks.

Overall, Kutools provides a straightforward interface for users who prefer visual selections over formulas. It is especially helpful for those less comfortable with complex Excel functions or who wish to perform batch operations on matching cells. However, selecting and highlighting do not automatically provide a column result unless you combine this with further steps.

Visually highlight cells if they begin or end with a specific character using Conditional Formatting

Conditional Formatting offers a simple and visual way to instantly see which cells start or end with a desired character, by automatically applying color or formatting rules to matching cells. This solution is ideal when you want an immediate, dynamic view of matches, but do not necessarily need a new column showing the result.

For example, to highlight cells beginning with “s” or ending with “n” in your list:

1. Select the range of cells you wish to format.

2. Go to the Home tab, click Conditional Formatting > New Rule.

3. Choose Use a formula to determine which cells to format.

4. To highlight cells that begin with “s”, enter the following formula:

=LEFT(TRIM(A2),1)="s"

5. Click the Format button, set your preferred fill color or font style, then click OK.

If you want to highlight cells that end with “n”, use this formula instead:

=RIGHT(TRIM(A2),1)="n"

6. Click OK through all dialog boxes. All applicable cells will now be visually highlighted according to your chosen rule.

Practical notes and troubleshooting:

• Make sure that the formula references (e.g., A2) match the first cell of your selection range. If your data range begins at a different row, adjust the formula accordingly.
• Conditional Formatting updates automatically as your data changes — if you add more rows, extend the rule accordingly.
• To remove the formatting, simply clear the rules from your selected cells via Conditional Formatting > Clear Rules.
• Conditional Formatting is an effective way to visually review data, but does not add logical columns or perform actions beyond formatting. Combine with sorting or filtering for more advanced tasks.
• If overlapping rules cause confusing results, review the order and priority of your formatting rules.

Demo: Check if cell begins or ends with a specific character with Kutools for Excel

Check if cell begins or ends with a specific character using VBA code

If you need to automate the process, handle large datasets, or prefer a more versatile, customizable approach, using a VBA macro can be highly effective. This is especially practical when you want to loop through a range, output results automatically to another column, or highlight cells meeting your criteria, all with a single action.

How to use:

1. Open the VBA editor by clicking Developer Tools > Visual Basic or directly press the Alt + F11 keys. In the VBA window, click Insert > Module, and then enter the appropriate code as shown below.

If you want to output the result ("OK"/"Not OK") in a new column for cells that begin with a specific character, use the following code:

Sub CheckCellStartCharacter()
    Dim WorkRng As Range
    Dim CheckChar As String
    Dim i As Long
    Dim OutCol As Integer
    
    On Error Resume Next
    Set WorkRng = Application.InputBox("Select the range to check", "KutoolsforExcel", Type:=8)
    CheckChar = InputBox("Enter the starting character to check (case-sensitive):", "KutoolsforExcel")
    
    If WorkRng Is Nothing Or CheckChar = "" Then Exit Sub
    
    OutCol = WorkRng.Columns(WorkRng.Columns.Count).Column + 1
    
    For i = 1 To WorkRng.Rows.Count
        If Left(Trim(WorkRng.Cells(i, 1).Value), 1) = CheckChar Then
            WorkRng.Cells(i, 1).Offset(0, WorkRng.Columns.Count).Value = "OK"
        Else
            WorkRng.Cells(i, 1).Offset(0, WorkRng.Columns.Count).Value = "Not OK"
        End If
    Next i
    
    MsgBox "Check complete. Results output in column " & Chr(65 + WorkRng.Columns.Count), vbInformation
End Sub

2. Close the VBA editor and return to Excel. Press F5 key or click Run. A prompt will ask you to select the range you want to analyze and the target starting character. The macro will then output "OK" or "Not OK" in the column immediately to the right of your selection.

If you would rather highlight cells that end with a specific character (instead of outputting a result), use the following alternative macro:

Sub HighlightCellsEndingWithChar()
    Dim WorkRng As Range
    Dim CheckChar As String
    Dim i As Long
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.InputBox("Select range to highlight", xTitleId, Type:=8)
    CheckChar = InputBox("Enter the ending character to highlight (case-sensitive):", xTitleId)
    If WorkRng Is Nothing Or CheckChar = "" Then Exit Sub
    For i = 1 To WorkRng.Rows.Count
        If Right(Trim(WorkRng.Cells(i, 1).Value), 1) = CheckChar Then
            WorkRng.Cells(i, 1).Interior.Color = vbYellow
        End If
    Next i
    MsgBox "Highlighting complete.", vbInformation
End Sub

To run: Follow the same process in the VBA editor, and execute HighlightCellsEndingWithChar. This will highlight in yellow any cell whose value ends with your specified character.

Tips and notes:

• All VBA code should be saved in a macro-enabled Excel file format (.xlsm) to ensure proper functionality.
• These macros are case-sensitive and trim spaces before checking characters. Adjust and customize within the code as needed.
• VBA is suitable for advanced users or for scenarios requiring batch processing, repeated operations, or workflow automation.
• Always back up your data before running macros in case of unintentional changes.