IsLetter Function for VBA


A few days ago I responded to a tweet from Sam Plett (@samplett) who was lamenting the difficulty of determining if a character is a letter or number in VBA. Here’s his exact tweet: Why is it so difficult in #vba to check if a character is a letter or a number – argh! I tweeted back saying that it isn’t that hard. VBA’s built-in IsNumeric function tells us if a character is a number. If the character isn’t a number, then it’s a character. I was wrong. When I read Sam’s tweet I fixated on the word character and failed to see letter. IsNumeric tells us if a character is a number. But, if the character isn’t a number, then we don’t know if it’s a letter, a symbol, punctuation, etc. Apologies to Sam for not paying closer attention to what he said and responding to quickly.

Sam is right. While VBA has native functions that test to see if an item is numeric (IsNumeric), is an object (IsObject), or is a date (IsDate), it does not have one that tests to see if an item is composed entirely of letters. That’s simple enough to correct.

The code below implements an IsLetter function. The code for this is simple. Loop through all the characters in a string checking the ASCII value of each one to see if it falls into the range 65 – 90 (uppercase A-Z) or 97 – 122 (lowercase a-z). If all the characters in the string fall into those ranges, then IsLetter returns True. If any character falls outside of those ranges, then IsLetter stops processing the remaining characters (no need to check them once we know the string is not composed of all letters) and returns False.

Function IsLetter(strValue As String) As Boolean
    Dim intPos As Integer
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function
Advertisements

46 comments on “IsLetter Function for VBA

  1. Hi David, thank you for this – it helps me complete an assignment. I am having some trouble with this though when adapting it for punctuation. Below is my code but it’s just skipping the case statement for any ASCII characters that match punctuation. I have tried putting ASCII 32 (space) in there and it still fails.

    
    Function IsPunctuation(VOTDText As String) As Boolean
        Dim intPos As Integer
        For intPos = 1 To Len(VOTDText)
            Select Case Asc(Mid(VOTDText, intPos, 1))
                Case 33 To 47, 58 To 63 'Checks for ASCII punctuation characters
                    IsPunctuation = True
                Case Else
                    IsPunctuation = False
                    Exit For
            End Select
        Next
    End Function
    

    Any assistance would be appreciated.

    • Hi, William.

      You’re welcome.

      The code is working properly, just not as I expect you want it to. The original code checks a string to see if it’s composed entirely of letters. If it is, then it returns “true”. Otherwise, it returns “false”. This function does the same thing only it checks for punctuation characters. Pass it a string composed solely of punctuation characters and it performs as it should. For example, Call the function and pass it a comma.

      Sub Test()
          debug.print IsPunctuation(",")
      End Sub
      

      The code will return “true”. Passing it a space won’t work because the ASCII code for a space is 32 and that’s outside the range (i.e. 33 to 47 and 58 to 63) the code is checking for. It will return “false”. Passing it any string that does not begin with a punctuation character will immediately cause it to fall through and return “false”. This happens because the code is designed to stop checking as soon as it encounters any non-punctuation character.

    • Thank you for that David, I’m not sure if my prior reply came through so I am typing it again. What I am trying to have happen is for the entire VBA code to do one of two things:

      1. Read in the entire array and when it finds a piece of punctuation, make sure there is a single space afterward
      ~OR~
      2. Read the cells row by row and when it finds a piece of punctuation…

      I came across your function and thought it would be very useful in this task. Mind giving me a little help on how I can modify your code to have it do the “finding a piece of punctuation” part?

    • Hi, William.

      Sorry to have taken so long to reply to you.

      You could read the text character by character and use that function to detect punctuation characters. On finding one you could then check to see if the next character is a space. If it’s not, then you could add one. That’s a very inefficient process though. It’s also not going to produce the desired results if the code is checking for every punctuation character. Some punctuation marks are only followed by a space in certain circumstances. For example, the first ” in a quote isn’t followed by a space, but the second one is. Apostrophes are followed by a space sometimes, but most of the time they aren’t. Periods are followed by a space except when they’re being used as a decimal point or when the period is part of an ellipses.

    • I appreciate the explanation. Let me re-phrase my question and explain briefly what I am trying to accomplish, after modifying your function. What I am looking for is either:
      – each line by line ‘string’ to be searched for a punctuation and then put a space after it
      – OR, the entire array to be searched for punctionation, then make sure there’s a space after it.

      My task is to check for punctuation/spacing errors, and I am hoping your function can come in handy!

      I hope this isn’t too much of an ask for you David – don’t want you to write the code for me! 🙂

  2. Hi David, What a great post! I was wondering as to whether this could be adapted to check if the text within a user populated field via a form could be tested to check if it conformed to the following alphanumeric conditions. For example, AANN-NAA, A being a letter of the alphabet and N being a number. If you can help me that would be great. :-)Thanks Anna.

    • Hi, Anna.

      Thanks!

      Yes, what you’ve described is possible. Two questions: will the match on the alpha characters be case sensitive, what do you want to do once the matching operation is complete?

  3. field3 in my access query combines field1 (a number) and field2 (letters) through the expression field1&” “&field2. I wish to establish field4 which looks at field 3 and returns only the number portion of field 3 (ie field4 will look like field1). How can I do this?

    I have tried the function Val([field3]) without success. It returns zero.

    • Hi, Valentine.

      There are probably more efficient ways to do this, but here’s one simple way to do it. This solution loops through each character in [Field3] checking to see if it is a letter. If it’s not, then it copies the character to a temporary string. If it is, then it exits the loop since the first position containing a letter means that all the numbers have been processed. This will only work as long as the pattern you described (i.e. some number of numbers followed by some number of letters) is present.

      For intPtr = 1 To Len([Field3])
          strChr = Mid([Field3], intPtr, 1)
          If IsLetter(strChr) Then
              Exit For
          Else
              strTmp = strTmp & strChr
          End If
      Next
      [Field4] = strTmp
      
  4. hi i have a name lee
    situation = if cells has name who start with L chrtr then shift to next shell othewise no action please suggest how to do this ?

    • Hi, Sachin.

      I need a little more information to answer your question. Since you mention “cells” can I assume that you want this to work in Excel? When you say “next shell” (I assume you meant “cell” and not “shell”), which cell is the “next cell”? The next one to the left, right, down, or something else?

  5. Hi David, thank you for sharing this & it is really wonderful. But I have a case where I need to find specific let’s say word which has 2 letters & 5 numbers as ” UK12345″ & these letters are changing regarding country & numbers as well. So is there any way to create function which can recognize this code? I need this because sometimes I’m looking for this code in cell which has more words as:
    In london UK12345 is ready
    Sales are closed in (UK12345)
    UK12345 is done

    do you think it is possible?

    • Muath,

      Then you want something like this

      Function FindText(strTextToSearch As String, strPatternToFind As String, bolCaseSensitive As Boolean) As String
          Set objRegEx = CreateObject("VBscript.RegExp")
          With objRegEx
              .IgnoreCase = Not bolCaseSensitive
              .Global = True
              .Pattern = strPatternToFind
              Set colMatches = .Execute(strTextToSearch)
          End With
          For Each varMatch In colMatches
              FindText = FindText & varMatch & "|"
          Next
          If Len(FindText) > 0 Then FindText = Left(FindText, Len(FindText) - 1)
          Set objRegEx = Nothing
          Set colMatches = Nothing
      End Function
      

      This function uses RegEx (regular expressions) to search a text string for a pattern. You’ll pass the function three parameters: the string to search, the pattern to search for, and a true or false indicating whether you want the search to be case sensitive or not. The pattern you’ll pass is “\b[A-Za-z]{2}[0-9]{5}\b”. This tells the function to find all occurrences of two letters followed by 5 numbers. The function will return a delimited string of matches. Here’s an example.

      strResult = FindString("In london UK12345 is ready", "\b[A-Za-z]{2}[0-9]{5}\b", False)
      

      The function would return “UK12345”.

      If there was more than one occurrence of the pattern int he string, for example

      strResult = FindString("In london UK12345 and FR98765 are ready", "\b[A-Za-z]{2}[0-9]{5}\b", False)
      

      then the function would return “UK12345|FR98765”. In order to handle that, you should use the Split function to put the results in an array and then read the array. Something like

      strResult = FindString("In london UK12345 and FR98765 is ready", "\b[A-Za-z]{2}[0-9]{5}\b", False)
      arrResult = Split(strResult, "|")
      For Each varResult in arrResult
         'Some code here to handle the individual results
      Next
      
    • Thank you David so much!!! it works so well! I’m new in VBA and I was using very simple function:
      Function mycode(myStr As String) As String

      mycode = Mid(myStr, InStr(myStr, “(“) + 1, 7)

      End Function
      But which you just created is amazing! So can you please explain me this function which you created? because it is the first time I see: Set objRegEx = CreateObject(“VBscript.RegExp”)

      Best regards,
      Muath

    • You’re welcome, Muath.

      Set objRegEx = CreateObject(“VBscript.RegExp”)

      The “Set” command is used to instantiate an object type variable. objRegEx is the name I gave that variable. There’s nothing special about the variable name, I could have used anything. CreateObject is a built-in function that does what the name implies: creates an object. In this case the object it is creating is from the VBScript runtime library and is of class RegExp. The name “RegExp” is short for regular expression. Regular expressions are present in many programming languages. It is a pattern matching routine that looks for a specified pattern in a given block of text. Does that help?

  6. The method proposed is perfect, if you consider only 26 letters [a-z] used in English. But depending on language, many characters with diacritic signs are letters as well. I suppose that the most reliable method in such situation would be to loop through a string with the Mid$ function and for each character compare UCase with LCase of the character (with the function StrComp(UCase(char), LCase(char), 0). If they happen to be the same (i.e. the function returns 0), the character is a non-letter and the string could be considered non-letter as a whole. Something like this:
    isLetter = True
    For intPos = 1 To Len(string)
    char = Mid$(string, intPos, 1)
    If StrComp(UCase(char), LCase(char), 0) = 0 Then isLetter = False
    isLetter = False Then Exit For
    Next intPos

    • Yes. I’d actually combine it with the previous IF statement, giving something like this

      Function IsLetter(strValue As String) As Boolean
          Dim intPos As Integer, strChr As String
          IsLetter = True
          For intPos = 1 To Len(strValue)
              strChr = Mid(strValue, intPos, 1)
              If StrComp(UCase(strChr), LCase(strChr), 0) = 0 Then
                  IsLetter = False
                  Exit For
              End If
          Next intPos
      End Function
      
    • Hi, Appenzeller.

      You’re correct. I wrote this solution for English language users. I like your solution. Thanks for sharing!

  7. THANK YOU SO MUCH!
    I’ve been trying to figure this out for my school assignment! Thank you thank you thank you! 🙂

  8. This was a perfect learning tool. this lead me to the function I was needing to check a single character within a string.

    Function IsLetter(varChr, notChr) As Boolean
    Select Case Asc(varChr)
    Case 65 To 90, 97 To 122
    Case Else
    notChr = True
    End Select
    End Function

    Thank you very much.

  9. I would add before the loop:

    ‘Checks to make sure input is not empty
    If strValue = Empty Then
    ‘If it is, return false
    IsLetter = False
    Exit Function
    End If

    Otherwise an error will occur if an empty input is given.

  10. Pingback: Is that a letter? - JP Software Technologies

  11. I would do this slightly differently. Since we know that an input string with at least one non-letter character will make the function False, assume that it’s true unless proven otherwise.

    Also, the Like function will serve the same purpose as checking the character code value.

    Function IsLetter(strValue As String) As Boolean
    Dim intPos As Long

    ‘ assume true
    IsLetter = True

    For intPos = 1 To Len(strValue)
    IsLetter = (Mid$(strValue, intPos, 1) Like “[a-zA-Z]”)
    If Not IsLetter Then Exit For ‘ Exit Function
    Next intPos
    End Function

    And here’s another version that might offer a few less processing cycles:

    Function IsLetter(strValue As String) As Boolean
    Dim intPos As Long

    ‘ assume true
    IsLetter = True

    Do Until IsLetter = False Or intPos = Len(strValue)
    intPos = intPos + 1
    IsLetter = (Mid$(strValue, intPos, 1) Like “[a-zA-Z]”)
    Loop
    End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s