VBA InStr - recognize code format ##.##.#### - no character

[hendrik1985]

Hi All,

In VBA I need to have a code which shows a number with the following format:
##.##.####

This must replace the Find.Spec formula, which is not available in VBA and I can not get the InStr function working with wildcards.
See example of the current status below.

I now have the following function:

Function dn(str1 As String) As String
Dim InstrWild As Long
If str1 Like "*" & "." & "*" Then
    InstrWild = InStr(10, str1, Split(".", "*")(0))
End If
dn = Mid(str1, InstrWild - 2, 10)
End Function

If a string contains a ".", then find the position of this "." and display a string with a length of 10, starting 2 signs before the found ".".

Now, the code needs to recognize only numbers instead of text. Line 3 in my attached example doesnt show up with the result I want. It needs to provide the same code as in the first line.
I hope my point is clear. Who can help me out please?

Thanks in advance!

Regards, Hendrik

 

[mikerickson]

You could use this UDF.
Matching Substring returns a sub-string of the inputString which matches the patternString.

e.g. =MatchingSubstring("abc123xyz", "###") returns "123"

The returnLength argument determines the length of the sub-string returned
If ommited the returnLength is the length of the pattern string

=MatchingSubstring("abc123xyz", "###", 2) returns "12"
=MatchingSubstring("abc123xyz", "*b", 3) returns "abc"
=MatchingSubstring("abc123xyz", "c*") returns "c1"

The incidence argument defaults to 1.

=MatchingSubstring("a1b2c3d", "#", ,1) returns "1"
=MatchingSubstring("a1b2c3d", "#", ,2) returns "2
=MatchingSubstring("a1b2c3d", "#", ,3) returns "3"
=MatchingSubstring("a1b2c3d", "#", ,4) returns ""

For your situation, a formula like =MatchingSubstring(A1,"##.##.#####") should work.

Function MatchingSubstring(inputString As String, PatternString As String, _
        Optional returnLength As Double = -1, Optional Incidence As Long) As String
    Dim i As Long
    If returnLength < 0 Then returnLength = Len(PatternString)
    If Incidence < 1 Then Incidence = 1
    For i = 1 To Len(inputString)
        If Mid(inputString, i) Like PatternString & "*" Then
            Incidence = Incidence - 1
            If Incidence = 0 Then
                MatchingSubstring = Mid(inputString, i, returnLength)
            End If
        End If
    Next i
End Function

 

[Krishnakumar]

Hi,
may be..

Function DN(ByVal str1 As String) As String
    Dim x, i As Long
    Const Patrn As String = "##.##.####"
    x = Split(str1, " ")
    For i = 0 To UBound(x)
        If x(i) Like Patrn Then DN = x(i): Exit Function
    Next
End Function

 

[hendrik1985]

Hi,
may be..

Function DN(ByVal str1 As String) As String
    Dim x, i As Long
    Const Patrn As String = "##.##.####"
    x = Split(str1, " ")
    For i = 0 To UBound(x)
        If x(i) Like Patrn Then DN = x(i): Exit Function
    Next
End Function

Thanks both for your help! This works.