[VBA] How to Determine the 3rd Character of a String

Introduction

In this article, I will explain how to determine whether the 3rd character of a string is an alphabet letter or a number using VBA.

Background

I encountered a case where strings contained a mix of alphabets, numbers, and symbols.

• Rule: The first two characters are always alphabets.
• Scenario:
  • If the 3rd character is also an alphabet, I want to extract the first 3 characters.
  • If the 3rd character is a number, I want to extract only the first 2 characters.

Since the 3rd character varies between letters and numbers, I needed a way to check it programmatically.

Functions Used

• Mid Function: Extracts a specific number of characters from a specified position in a string.
• IsNumeric Function: Determines if an expression can be evaluated as a number.

By combining these two, we can implement the required logic.

Actual Code

Sub CheckThirdCharacter()

    Dim myString As String
    Dim thirdChar As String

    ' Sample string
    myString = "AA123"
    
    ' Extract 1 character starting from the 3rd position
    thirdChar = Mid(myString, 3, 1)

    ' Check if the extracted character is a number
    If IsNumeric(thirdChar) Then
        ' If it is a number, keep only the first 2 characters
        myString = Left(myString, 2)
        Range("A1").Value = myString
    Else
        ' If it is not a number (i.e., alphabet), keep the first 3 characters
        myString = Left(myString, 3)
        Range("A1").Value = myString
    End If

End Sub

Code Explanation

1. Variable Declaration: myString holds the target text, and thirdChar holds the character to be checked.
2. Mid(myString, 3, 1): This function looks at myString, starts at the 3rd character, and extracts 1 character. In the example “AA123”, it extracts “1”.
3. IsNumeric(thirdChar): This checks if “1” is a number. It returns True.
4. If Statement:
   • Since it is a number (True), the code executes Left(myString, 2). The result “AA” is output to cell A1.
   • If the string were “ABC12”, the 3rd char is “C”. IsNumeric would be False, so Left(myString, 3) would execute, outputting “ABC”.

Why the “Like” Operator Failed

I initially attempted to use the Like operator combined with Left. I thought I could extract the first 3 characters and check if it matched a pattern like [A-Z].

However, the Like operator is designed for pattern matching against string structures, making it clumsy for pinpointing the data type of a specific single character index compared to the direct approach of Mid.

Consequently, the combination of Mid (to target the position) and IsNumeric (to check the type) proved to be the most reliable method.

Summary

To determine if a specific character in a string is a number or a letter:

1. Use Mid to extract the character at the specific position.
2. Use IsNumeric to check the data type of that character.

This method allows for flexible string manipulation rules, such as changing the extraction length based on the content of the data.