Find lengh of string between . and .

[Shadow123]

I would like to look at each cell in column C, and determine if there are 5 char between the first . and the second .

I can do the for each cell in range part, but i cant make the . to . length work. For arguements sake lets assume that i want to display the value in column C in a message box if the there are 5 char between the first dot and the second dot in the string.

Eg
ABC.APR13.34235.O (This one shows up in message box)
ABCD.D(C).23123.T (This one doesnt show up as its 4 char between dots)

 

[JoeMo]

Assumes your data start in cell A1 - adjust to suit.

Sub BetweenTheDots()
Dim lR As Long, c As Range
Dim firstDot As Long, secondDot As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
'assume data start in A1 - adjust to suit
For Each c In Range("A1", "A" & lR)
    firstDot = InStr(c.Value, ".")
    If firstDot > 0 Then
        secondDot = InStr(firstDot + 1, c.Value, ".")
        If secondDot > 0 Then
            If secondDot - firstDot = 6 Then
                MsgBox Mid(c.Value, firstDot + 1, 5) & " found in cell " & c.Address
            Else
                MsgBox "Number of characters between first two dots in cell " & c.Address & " is not = 5"
            End If
        Else
            MsgBox "Only one dot found"
        End If
    Else
        MsgBox "No dots found"
    End If
Next c
End Sub

 

[Shadow123]

done, thanks very much

 

[JoeMo]

done, thanks very much

You are welcome.

 

[JLGWhiz]

ADVERTISEMENT

Another way
If we could assume that the last eight characters will always be .#####.A then:

Sub test()
Dim c As Range
With ActiveSheet
For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
    If Len(c) - (InStr(c, ".") + 8) = 5 Then
        MsgBox c.Address
    End If
Next
End With
End Sub

 

[Kyle123]

Maybe:

Sub test()
Dim c As Range
With ActiveSheet
For Each c In .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
    If Len(split(c,".")(1)) = 5 Then
        MsgBox c.Address
    End If
Next
End With
End Sub

 

[Robert Mika]

=IF(LEN(TRIM(LEFT(RIGHT(SUBSTITUTE("."&A1,".",REPT(" ",200)),600),200)))=5,"Yes","No")

 

[slinky]

Instr can be teamed with InstrRev in VBA.. InstrRev is the Right to Instr's Left ..

From an immediates window..
?instr(1,"1.11111.1",".")
2
?instrrev("1.11111.1",".")
8

Hope that helps..