Find first non-numeric character in string

[scotthutchings]

I need to separate the numeric portion and non-numeric portions of a string so I can use the numeric portion in calculations and then re-attach the non-numeric portion when the calculations are complete.

For example, if a user enters the following string to represent an opening number:

100-103,105

I use the following code to determine that I have (5) openings, namely 100,101,102,103, and 105.

strElements = Split(Openings, ",")
    For lngSubscript = LBound(strElements) To UBound(strElements)
        'Check for Range of numbers separated by '-'
        lngPos = InStr(strElements(lngSubscript), "(")
        strName = Trim(strElements(lngSubscript))
        If strName Like "*-*" Then
            strElements2 = Split(strName, "-")
            For lngSubscript2 = LBound(strElements2) To UBound(strElements2)
            'Get Starting and ending values
                If StartingValue = 0 Then
                    StartingValue = Trim(strElements2(lngSubscript2))
                Else
                    EndingValue = Trim(strElements2(lngSubscript2))
                End If
            Next lngSubscript2
            OpeningRange = Abs(EndingValue) - Abs(StartingValue)
            OpeningCount = OpeningCount + OpeningRange
        End If
        OpeningCount = OpeningCount + 1
    Next lngSubscript
CountOpenings = OpeningCount

The problem is that the input string could be 100A-103A, 105. In this case, I need the total number of openings to still be 5, but I need the openings to be 100A, 101A, 102A, 103A and 105. The same would be true if the use entered 100A1-103A1, 105, or 100STAIR-103STAIR, etc. The door numbers may be any numeric value (i.e. 1001A-1003A, or 1a-3a, etc.)

Thanks for your help.

 

[vbaInet]

Here are the steps, using 100A-103A as an example:

1. Use InStr() to get the position of "-"
2. Val() will get you the first part that is numeric which is 100
3. Use the character position (+ 1) gotten from step 1 in a For loop that will check each character. The functions you will need here are Mid() and Len(), then you need IsNumeric() to check if that character is numeric (as the name implies).

I hope that helps.