Interesting Observation

[Greg Maxey]

I was playing around with some code to format simple numbers like 1234 as 1,234 etc. I didn't want to process numbers like phone numbers 1-800-867-5309, or SSNs 123-45-6789 or serial numbers like 12345ERT56789WWQ1234 etc. I figured I could first find a group of 4 or more numbers and then check the preceeding and following characters and only process appropriate ranges.

In the code below, you will notice that I used Asc(oRng.Characters.Last.Next) and Asc(oRng.Characters.First.Previous) in my Select Case statements.

At first I tried just oRng.Characters.Last.Next and oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9), etc., in the Case statements. To my dismay I discovered that it was not processing numbers found in a table cell. I am not sure, but I have heard that an end of cell marker is a composite Chr(7) and Chr(13). I just figured that .Next or .Previsous would be either a Chr(7) or Chr(13). When it didn't work I used: MsgBox Asc(oRng.Characters.Last.Next) in a Case Else statement and confirmed that "13" was returned. With this knowledge I changed the code accordingly.

I think it is interesting that it works one way and not the other when on the surface it looks the same. There must be some type of conversion going on with the Asc statement that I don't understand. If anyone cares to enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub

 

[Doug Robbins - Word MVP]

Hi Greg,

I would use:

Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<[0-9]{4}>", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
myrange.Start = myrange.Start - 1
If Not Left(myrange, 1) = "-" Then
myrange.Start = myrange.Start + 1
myrange = Format(myrange, "#,###")
Else
myrange.Collapse wdCollapseEnd
End If
Loop
End With

Seems to me it does what you want even if the number is in a table.
--
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

I was playing around with some code to format simple numbers like 1234 as
1,234 etc. I didn't want to process numbers like phone numbers
1-800-867-5309, or SSNs 123-45-6789 or serial numbers like
12345ERT56789WWQ1234 etc. I figured I could first find a group of 4 or more
numbers and then check the preceeding and following characters and only
process appropriate ranges.

In the code below, you will notice that I used
Asc(oRng.Characters.Last.Next) and Asc(oRng.Characters.First.Previous) in my
Select Case statements.

At first I tried just oRng.Characters.Last.Next and
oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9), etc., in
the Case statements. To my dismay I discovered that it was not processing
numbers found in a table cell. I am not sure, but I have heard that an end
of cell marker is a composite Chr(7) and Chr(13). I just figured that .Next
or .Previsous would be either a Chr(7) or Chr(13). When it didn't work I
used: MsgBox Asc(oRng.Characters.Last.Next) in a Case Else statement and
confirmed that "13" was returned. With this knowledge I changed the code
accordingly.

I think it is interesting that it works one way and not the other when on
the surface it looks the same. There must be some type of conversion going
on with the Asc statement that I don't understand. If anyone cares to
enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub

 

[Greg Maxey]

Doug,

That method process patterns that I don't want processed. I don't want to
process number patterns like:

1.800.867.5309 or 2/04/2006 or 123-45-6789 or 123DEF123456798OT etc.

The only way I can see to do that is compare the character before and after
the found range to "space" separators like tabs, spaces, line breaks, end of
cell marks, etc.

I found it interesting that:

Case Select Asc(oRng.Characters.Last.Next)
Case 13

detects and end of cell while

Case Select oRng.Characters.Last.Next
Case Chr(13)

doesn't.



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

I would use:

Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<[0-9]{4}>", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
myrange.Start = myrange.Start - 1
If Not Left(myrange, 1) = "-" Then
myrange.Start = myrange.Start + 1
myrange = Format(myrange, "#,###")
Else
myrange.Collapse wdCollapseEnd
End If
Loop
End With

Seems to me it does what you want even if the number is in a table.
--
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

I was playing around with some code to format simple numbers like 1234 as
1,234 etc. I didn't want to process numbers like phone numbers
1-800-867-5309, or SSNs 123-45-6789 or serial numbers like
12345ERT56789WWQ1234 etc. I figured I could first find a group of 4 or
more numbers and then check the preceeding and following characters and
only process appropriate ranges.

In the code below, you will notice that I used
Asc(oRng.Characters.Last.Next) and Asc(oRng.Characters.First.Previous) in
my Select Case statements.

At first I tried just oRng.Characters.Last.Next and
oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9), etc.,
in the Case statements. To my dismay I discovered that it was not
processing numbers found in a table cell. I am not sure, but I have heard
that an end of cell marker is a composite Chr(7) and Chr(13). I just
figured that .Next or .Previsous would be either a Chr(7) or Chr(13).
When it didn't work I used: MsgBox Asc(oRng.Characters.Last.Next) in a
Case Else statement and confirmed that "13" was returned. With this
knowledge I changed the code accordingly.

I think it is interesting that it works one way and not the other when on
the surface it looks the same. There must be some type of conversion
going on with the Asc statement that I don't understand. If anyone cares
to enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub





--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Peter Jamieson]

Hi Greg,

The documentation suggestions that "Last" returns the last /character/ when
applied to "Characters", but in fact it returns a range containing two
characters, the first of which is a 13 and the second of which is a 7. I
won't attempt to check whether that, and the behaviour of Chr etc. is
consistent with your results, but maybe that's enough for you to make
progress (don't forget that there are also AscW and ChrW functions for
delaing with wide/Unicode characters.

Peter Jamieson

Doug,

That method process patterns that I don't want processed. I don't want to
process number patterns like:

1.800.867.5309 or 2/04/2006 or 123-45-6789 or 123DEF123456798OT etc.

The only way I can see to do that is compare the character before and
after the found range to "space" separators like tabs, spaces, line
breaks, end of cell marks, etc.

I found it interesting that:

Case Select Asc(oRng.Characters.Last.Next)
Case 13

detects and end of cell while

Case Select oRng.Characters.Last.Next
Case Chr(13)

doesn't.



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

I would use:

Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<[0-9]{4}>", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
myrange.Start = myrange.Start - 1
If Not Left(myrange, 1) = "-" Then
myrange.Start = myrange.Start + 1
myrange = Format(myrange, "#,###")
Else
myrange.Collapse wdCollapseEnd
End If
Loop
End With

Seems to me it does what you want even if the number is in a table.
--
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

I was playing around with some code to format simple numbers like 1234
as 1,234 etc. I didn't want to process numbers like phone numbers
1-800-867-5309, or SSNs 123-45-6789 or serial numbers like
12345ERT56789WWQ1234 etc. I figured I could first find a group of 4 or
more numbers and then check the preceeding and following characters and
only process appropriate ranges.

In the code below, you will notice that I used
Asc(oRng.Characters.Last.Next) and Asc(oRng.Characters.First.Previous) in
my Select Case statements.

At first I tried just oRng.Characters.Last.Next and
oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9), etc.,
in the Case statements. To my dismay I discovered that it was not
processing numbers found in a table cell. I am not sure, but I have
heard that an end of cell marker is a composite Chr(7) and Chr(13). I
just figured that .Next or .Previsous would be either a Chr(7) or
Chr(13). When it didn't work I used: MsgBox
Asc(oRng.Characters.Last.Next) in a Case Else statement and confirmed
that "13" was returned. With this knowledge I changed the code
accordingly.

I think it is interesting that it works one way and not the other when on
the surface it looks the same. There must be some type of conversion
going on with the Asc statement that I don't understand. If anyone cares
to enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub





--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Greg Maxey]

Peter,

That is interesting.

While:
Select Case Asc(oRng.Characters.Last.Next)
Case 13

does detect the end of cell marker
and

Select Case oRng.Characters.Last.Next
Case Chr(13)

doesn't detect the marker

Select Case oRng.Characters.Last.Next
Case Chr$(13) & Chr$(7)

does!!


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

The documentation suggestions that "Last" returns the last
/character/ when applied to "Characters", but in fact it returns a
range containing two characters, the first of which is a 13 and the
second of which is a 7. I won't attempt to check whether that, and
the behaviour of Chr etc. is consistent with your results, but maybe
that's enough for you to make progress (don't forget that there are
also AscW and ChrW functions for delaing with wide/Unicode characters.

Peter Jamieson

Doug,

That method process patterns that I don't want processed. I don't
want to process number patterns like:

1.800.867.5309 or 2/04/2006 or 123-45-6789 or 123DEF123456798OT etc.

The only way I can see to do that is compare the character before and
after the found range to "space" separators like tabs, spaces, line
breaks, end of cell marks, etc.

I found it interesting that:

Case Select Asc(oRng.Characters.Last.Next)
Case 13

detects and end of cell while

Case Select oRng.Characters.Last.Next
Case Chr(13)

doesn't.



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

I would use:

Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<[0-9]{4}>", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
myrange.Start = myrange.Start - 1
If Not Left(myrange, 1) = "-" Then
myrange.Start = myrange.Start + 1
myrange = Format(myrange, "#,###")
Else
myrange.Collapse wdCollapseEnd
End If
Loop
End With

Seems to me it does what you want even if the number is in a table.
--
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

I was playing around with some code to format simple numbers like
1234 as 1,234 etc. I didn't want to process numbers like phone
numbers 1-800-867-5309, or SSNs 123-45-6789 or serial numbers like
12345ERT56789WWQ1234 etc. I figured I could first find a group of
4 or more numbers and then check the preceeding and following
characters and only process appropriate ranges.

In the code below, you will notice that I used
Asc(oRng.Characters.Last.Next) and
Asc(oRng.Characters.First.Previous) in my Select Case statements.

At first I tried just oRng.Characters.Last.Next and
oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9),
etc., in the Case statements. To my dismay I discovered that it
was not processing numbers found in a table cell. I am not sure,
but I have heard that an end of cell marker is a composite Chr(7)
and Chr(13). I just figured that .Next or .Previsous would be
either a Chr(7) or Chr(13). When it didn't work I used: MsgBox
Asc(oRng.Characters.Last.Next) in a Case Else statement and
confirmed that "13" was returned. With this knowledge I changed
the code accordingly.

I think it is interesting that it works one way and not the other
when on the surface it looks the same. There must be some type of
conversion going on with the Asc statement that I don't understand.
If anyone cares to enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub





--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Ed]

Greg: Just some additional info, FWIW

When I tried to put your observation into an If statement as such:
If rng.Characters.Last.Next = chr$(13) & chr$(7) Then
rng.MoveEnd Unit:=wdCharacter, Count:=-2
End If
it actually gave me the first character of the next table cell!

I changed to:
If rng.Characters.Last= chr$(13) & chr$(7) Then
rng.MoveEnd Unit:=wdCharacter, Count:=-2
End If
and it returned the correct response - except the "-2" count went one
character past the marker. Byt changing that to "-1", I was able to reset
the range to cover only the text in a cell without the marker. Apparently,
the end of cell marker consists of two characters, but is seena dn counted
by Word as only one character.

Ed

Peter,

That is interesting.

While:
Select Case Asc(oRng.Characters.Last.Next)
Case 13

does detect the end of cell marker
and

Select Case oRng.Characters.Last.Next
Case Chr(13)

doesn't detect the marker

Select Case oRng.Characters.Last.Next
Case Chr$(13) & Chr$(7)

does!!


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

The documentation suggestions that "Last" returns the last
/character/ when applied to "Characters", but in fact it returns a
range containing two characters, the first of which is a 13 and the
second of which is a 7. I won't attempt to check whether that, and
the behaviour of Chr etc. is consistent with your results, but maybe
that's enough for you to make progress (don't forget that there are
also AscW and ChrW functions for delaing with wide/Unicode characters.

Peter Jamieson

Doug,

That method process patterns that I don't want processed. I don't
want to process number patterns like:

1.800.867.5309 or 2/04/2006 or 123-45-6789 or 123DEF123456798OT etc.

The only way I can see to do that is compare the character before and
after the found range to "space" separators like tabs, spaces, line
breaks, end of cell marks, etc.

I found it interesting that:

Case Select Asc(oRng.Characters.Last.Next)
Case 13

detects and end of cell while

Case Select oRng.Characters.Last.Next
Case Chr(13)

doesn't.



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Hi Greg,

I would use:

Dim myrange As Range
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<[0-9]{4}>", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
myrange.Start = myrange.Start - 1
If Not Left(myrange, 1) = "-" Then
myrange.Start = myrange.Start + 1
myrange = Format(myrange, "#,###")
Else
myrange.Collapse wdCollapseEnd
End If
Loop
End With

Seems to me it does what you want even if the number is in a table.
--
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

I was playing around with some code to format simple numbers like
1234 as 1,234 etc. I didn't want to process numbers like phone
numbers 1-800-867-5309, or SSNs 123-45-6789 or serial numbers like
12345ERT56789WWQ1234 etc. I figured I could first find a group of
4 or more numbers and then check the preceeding and following
characters and only process appropriate ranges.

In the code below, you will notice that I used
Asc(oRng.Characters.Last.Next) and
Asc(oRng.Characters.First.Previous) in my Select Case statements.

At first I tried just oRng.Characters.Last.Next and
oRng.Characters.First.Previous and then usied Case Chr(7), Chr (9),
etc., in the Case statements. To my dismay I discovered that it
was not processing numbers found in a table cell. I am not sure,
but I have heard that an end of cell marker is a composite Chr(7)
and Chr(13). I just figured that .Next or .Previsous would be
either a Chr(7) or Chr(13). When it didn't work I used: MsgBox
Asc(oRng.Characters.Last.Next) in a Case Else statement and
confirmed that "13" was returned. With this knowledge I changed
the code accordingly.

I think it is interesting that it works one way and not the other
when on the surface it looks the same. There must be some type of
conversion going on with the Asc statement that I don't understand.
If anyone cares to enlighten, I am all attention. Thanks.

Sub CommaFormatNumbers()

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9]{4,})"
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
While .Execute
Select Case Asc(oRng.Characters.Last.Next)
Case 7, 9, 10, 11, 13, 32
On Error GoTo Handler
Select Case Asc(oRng.Characters.First.Previous)
Case 7, 9, 10, 11, 13, 32
Proceed:
oRng = Format$(oRng, "#,##0")
oRng.Collapse wdCollapseEnd
Case Else
oRng.Collapse wdCollapseEnd
End Select
Case Else
'Do Nothing
End Select
Wend
End With
Exit Sub
Handler:
Resume Proceed
End Sub





--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Greg]

Ed,

Jonathan West explained it to me as follows a while back:

7. Jonathan West
Oct 7 2005, 6:31 am show options

Newsgroups: microsoft.public.word.vba.general
From: "Jonathan West" <[email protected]> - Find messages by this author
Date: Fri, 7 Oct 2005 12:31:08 +0100
Local: Fri, Oct 7 2005 6:31 am
Subject: Re: Strip End Cell Marker
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse

Doug, Helmut

Thanks. I think it interesting that the cell marker has a length of 2
characters illustrated in example two, a width of only one wdCharacter as
illustrated in examples two and three, and in fact consists of two
characters as illustrated in example 4. Seems to disprove the thought
that two things can't occupy the same space at the same time

The reasons for the apparent confusion is that the same name
"character" is
being used for two completely different entities.

Within a string, a character is an unformatted single printable or
unprintable item. The item is a representation of an 8-bit ANSI or
16-bit
Unicode code as appropriate.


Within the document a Character is a particular kind of Range object,
containing formatting information as well as (optionally) printable
text.


Converting a document Character to a string character always results in
the
formatting information being stripped off, and sometimes results in one

character being included in the string, sometimes two.


--

 

[Ed]

Thanks, Greg. Makes as much sense as anything else in Word! <g>
Ed