VBA to find third decimal point?

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
Tue Jul 19 15:47:52 CDT 2005

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
"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:ezgZBZJjFHA.1372@TK2MSFTNGP10.phx.gbl...
> 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
>
>

Helmut
Tue Jul 19 15:52:18 CDT 2005

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.




>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
>

David
Tue Jul 19 15:54:26 CDT 2005

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
Tue Jul 19 16:11:25 CDT 2005

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

"Helmut Weber" <red.sys@t-online.de> wrote in message
news:ivmqd1lppkcdpiedl5qlu5qp6p9obpese2@4ax.com...
> 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.
>
>
>
>
> >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
> >
>

Jay
Tue Jul 19 22:54:24 CDT 2005

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 FAQ: http://word.mvps.org

On Tue, 19 Jul 2005 22:47:52 +0200, "Doug Robbins"
<dkr@REMOVEmvps.org> wrote:

>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
>"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
>news:ezgZBZJjFHA.1372@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>

HelmutWeber
Wed Jul 20 04:16:04 CDT 2005

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
Wed Jul 20 07:59:33 CDT 2005

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

"Helmut Weber" <HelmutWeber@discussions.microsoft.com> wrote in message
news:1DD766EA-229F-4B50-8752-A716BA5DDCB5@microsoft.com...
> 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
Wed Jul 20 08:12:36 CDT 2005

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

"Jay Freedman" <jay.freedman@verizon.net> wrote in message
news:baird1546al3d9qas058v86osjqpji3b8g@4ax.com...
> 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 FAQ: http://word.mvps.org
>
> On Tue, 19 Jul 2005 22:47:52 +0200, "Doug Robbins"
> <dkr@REMOVEmvps.org> wrote:
>
> >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
> >"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> >news:ezgZBZJjFHA.1372@TK2MSFTNGP10.phx.gbl...
> >> 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
> >>
> >>
> >
>

Jay
Wed Jul 20 09:01:14 CDT 2005

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 FAQ: http://word.mvps.org

Ed wrote:
> 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
>
> "Jay Freedman" <jay.freedman@verizon.net> wrote in message
> news:baird1546al3d9qas058v86osjqpji3b8g@4ax.com...
>> 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 FAQ: http://word.mvps.org
>>
>> On Tue, 19 Jul 2005 22:47:52 +0200, "Doug Robbins"
>> <dkr@REMOVEmvps.org> wrote:
>>
>>> 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
>>> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
>>> news:ezgZBZJjFHA.1372@TK2MSFTNGP10.phx.gbl...
>>>> 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