VBA to find third decimal point?

[Ed]

The cells in one column of a table contain numbers separated by decimal
points. The numbers can have several points (1.2.3.4.5), or none. I would
like to go through each of these cells and truncate the number just before
the third decimal point, if it exists; if not, the number would be left
alone.

What method do I need to find the third decimal point in these strings?

Thanks.
Ed

 

[Doug Robbins]

Not quite sure what you want to end up with, but you may be able to use a
Wildcard Replace

See the article "Finding and replacing characters using wildcards" at:

http://word.mvps.org/FAQs/General/UsingWildcards.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Ed,

do you want what is left of the third dot,
if there are 3 or more dots,
or what is right of the third dot,
if there are 3 or ore dots.

 

[David Sisson]

Look at the INSTR function.

The number it returns is the starting place of the desired search
string. Then, have the result of the INSTR function, you can
manipulate the string using MID function.

 

[Ed]

Thanks for the response, Helmut. If the number has three or more decimal
points, I want everything that is left of the third point. 1.2.3.4.5 would
truncate to 1.2.3 and so forth.
Ed

 

[Jay Freedman]

Hey Ed,

Try this one:

Sub foo()
Dim oCell As Cell
Dim oRg As Range

For Each oCell In ActiveDocument.Tables(1).Columns(2).Cells
Set oRg = oCell.Range
With oRg.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]@.[0-9]@.[0-9]@)([.])([0-9.]{1,})"
.Replacement.Text = "\1"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
Next ocell
End Sub

 

[Helmut Weber]

Hi Ed,

besides of Jay's impressive wildcard search,
you might like this one, too, though perhaps
more useful in other cases,
which returns the start of the n-th string in another string,
hopefully.

Public Function GetPos( _
strTmp As String, strX As String, lngX As Long) As Long
' strtmp the string to search in
' strX the string to be found
' lngX the n-th occurence of the string to be found
Dim strArr() As String
Dim l As Long ' just a counter
Dim p As Long ' the position of the
' n-th occurence of the string to be found
strArr = Split(strTmp, strX)
If UBound(strArr) = 0 Then
GetPos = 0
Exit Function
End If
If UBound(strArr) = -1 Then
GetPos = -1
Exit Function
End If
If UBound(strArr) < lngX Then
GetPos = 0
Exit Function
End If
For l = 0 To lngX - 1
p = p + Len(strArr(l)) + Len(strX)
Next
p = p - Len(strX) + 1
GetPos = p
End Function

Sub test8773()
MsgBox GetPos("1.2.3.4.5", ".", 3)
MsgBox GetPos("1..2..3..4..5", "..", 3)
MsgBox GetPos("121xf.2.ac121vw..ertejtkle", "121", 2)
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Ed]

Wow! Thank you very much, Helmut! I appreciate the help.
Ed

 

[Ed]

When Doug suggested a Wildcard search, I didn't know how to implement it to
truncate a string, so I let it go. But that, as Helmut said, was
impressive! Thank you!
Ed

Hey Ed,

Try this one:

Sub foo()
Dim oCell As Cell
Dim oRg As Range

For Each oCell In ActiveDocument.Tables(1).Columns(2).Cells
Set oRg = oCell.Range
With oRg.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]@.[0-9]@.[0-9]@)([.])([0-9.]{1,})"
.Replacement.Text = "\1"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
Next ocell
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP

Not quite sure what you want to end up with, but you may be able to use a
Wildcard Replace

See the article "Finding and replacing characters using wildcards" at:

http://word.mvps.org/FAQs/General/UsingWildcards.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jay Freedman]

You're welcome.

By the way, developing that search string was not without its surprises. In
the last segment of the expression,
([0-9.]{1,})
originally I tried using the @ sign as in the other parts. According to the
help and both of the articles
http://word.mvps.org/FAQs/General/UsingWildcards.htm and
http://www.gmayor.com/replace_using_wildcards.htm, the @ sign and {1,} both
mean "one or more of the previous expression". But apparently @ uses "lazy"
evaluation (quitting as soon as it finds one occurrence), while {1,} uses
"greedy" evaluation (it keeps going until it finds a non-match). For a
typical IP address, 192.168.222.150, the expression with the @ sign results
in the replacement 192.168.22250 because it matches the third period and
just the single digit after it. The expression with {1,} matches the third
period and everthing else to the end of the address.

Also, there can be a difference between my approach and Helmut's when the
text in the table contains doubled periods in the first few, like
192..168.222.150 or 192.168..222.150. My search expression won't touch that
at all, because the double periods don't match anything in the expression.
If you rely on Helmut's function to find the third period, it will do
exactly that. Which approach you prefer depends on what you're doing with
the document and how confident you are that it doesn't contain any
typographical errors.

--
Regards,
Jay Freedman
Microsoft Word MVP

When Doug suggested a Wildcard search, I didn't know how to implement
it to truncate a string, so I let it go. But that, as Helmut said,
was impressive! Thank you!
Ed

Hey Ed,

Try this one:

Sub foo()
Dim oCell As Cell
Dim oRg As Range

For Each oCell In ActiveDocument.Tables(1).Columns(2).Cells
Set oRg = oCell.Range
With oRg.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]@.[0-9]@.[0-9]@)([.])([0-9.]{1,})"
.Replacement.Text = "\1"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
Next ocell
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP

Not quite sure what you want to end up with, but you may be able to
use a Wildcard Replace

See the article "Finding and replacing characters using wildcards"
at:

http://word.mvps.org/FAQs/General/UsingWildcards.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of
my services on a paid consulting basis.

Doug Robbins - Word MVP
The cells in one column of a table contain numbers separated by
decimal points. The numbers can have several points (1.2.3.4.5),
or none. I would
like to go through each of these cells and truncate the number
just before the third decimal point, if it exists; if not, the
number would be left alone.

What method do I need to find the third decimal point in these
strings?

Thanks.
Ed