Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA. How to find position of first digit in string

Tags:

vba

digit

I have string "ololo123". I need get position of first digit - 1. How to set mask of search ?

like image 313
Ololovin Avatar asked Aug 23 '10 13:08

Ololovin


People also ask

How do you find the position of a character in a string in Excel VBA?

InStr Function in Excel VBAThe VBA InStr function helps find the position of a given substring within a string. It returns the first occurrence of the substring in the form of an integer (output). A string is a series of characters or text supplied to the function in double quotation marks.

How do I find the first number in a string?

To get the first number in a string:Use the search() method to get the index of the first number in the string. The search method takes a regular expression and returns the index of the first match in the string. Access the string at the index, using bracket notation.

How do I extract a number from a string in Excel VBA?

Extract Numbers from String in Excel (using VBA) Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetNumeric(A2). This will instantly give you only the numeric part of the string.

How does InStr work in VBA?

InStr function finds the position of a specified substring within the string and returns the first position of its occurrence. For example, if you want to find the position of 'x' in 'Excel', using the Excel VBA InStr function would return 2.


2 Answers

Here is a lightweight and fast method that avoids regex/reference additions, thus helping with overhead and transportability should that be an advantage.

Public Function GetNumLoc(xValue As String) As Integer

For GetNumLoc = 1 To Len(xValue)
    If Mid(xValue, GetNumLoc, 1) Like "#" Then Exit Function
Next

GetNumLoc = 0

End Function
like image 138
Spere Avatar answered Nov 06 '22 17:11

Spere


Something like this should do the trick for you:

Public Function GetPositionOfFirstNumericCharacter(ByVal s As String) As Integer
    For i = 1 To Len(s)
        Dim currentCharacter As String
        currentCharacter = Mid(s, i, 1)
        If IsNumeric(currentCharacter) = True Then
            GetPositionOfFirstNumericCharacter = i
            Exit Function
        End If
    Next i
End Function

You can then call it like this:

Dim iPosition as Integer
iPosition = GetPositionOfFirstNumericCharacter("ololo123")
like image 45
Rob Avatar answered Nov 06 '22 18:11

Rob