Find lengh of string between . and .

Shadow123

Board Regular
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)

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JoeMo

MrExcel MVP
Assumes your data start in cell A1 - adjust to suit.
Code:
``````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

Board Regular
done, thanks very much

JLGWhiz

Well-known Member
ADVERTISEMENT
Another way
If we could assume that the last eight characters will always be .#####.A then:
Code:
``````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

Well-known Member
Maybe:
Code:
``````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

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

slinky

Active Member
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..

Replies
6
Views
195
Replies
2
Views
93
Replies
16
Views
440
Replies
23
Views
343
Replies
3
Views
260

Threads
1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back