Convert MMDDYY to Text--Need with with my Macro

K

Kim

This macro will just change the text in the selected text, but it will
jump to the next date found in the document, which I don't want it to
do. Also, other times it will change the date in the selected text AND
change dates everywhere in the document. I have very large documents
and when it changes all dates in it that really takes me a long time
to undo all things I didn't want to change. Please help!

Sub Convert_MMDDYY_to_Text()

Dim trange As Range
Dim frange As Range
Set frange = Selection.Range
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With

Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\-[0-9]{1,2}\-[0-9]{2,4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With

Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\-[0-9]{1,2}\-[0-9]{4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With

Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting

End Sub
 
G

Greg Maxey

Kim,

If all you want to do is change the format of the "selected" date then
trash that convoluted macro and use something like:

Sub ConvertDateFormat()
If IsDate(Selection.Range) Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
End Sub
 
K

Kim

Kim,

If all you want to do is change the format of the "selected" date then
trash that convoluted macro and use something like:

Sub ConvertDateFormat()
If IsDate(Selection.Range) Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
End Sub

This macro will just change the text in the selected text, but it will
jump to the next date found in the document, which I don't want it to
do. Also, other times it will change the date in the selected text AND
change dates everywhere in the document. I have very large documents
and when it changes all dates in it that really takes me a long time
to undo all things I didn't want to change. Please help!
Click to expand...
Sub Convert_MMDDYY_to_Text()
Click to expand...
Dim trange As Range
Dim frange As Range
Set frange = Selection.Range
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With
Click to expand...
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\-[0-9]{1,2}\-[0-9]{2,4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With
Click to expand...
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\-[0-9]{1,2}\-[0-9]{4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With
Click to expand...
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}",
MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True) = True
Set trange = Selection.Range
If trange.Start < frange.End Then
trange.Text = Format(trange.Text, "MMMM d, yyyy")
Else
Exit Sub
End If
Loop
End With
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Click to expand...
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks, that worked!
 
K

Kim

Kim,

If all you want to do is change the format of the "selected" date then
trash that convoluted macro and use something like:

Sub ConvertDateFormat()
If IsDate(Selection.Range) Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
End Sub
Click to expand...
The above works great if I only have the date highlighted. Most of the
text I need to convert is within a paragraph with running text or
table cells. Is it possible to select the paragraph/cell that the
dates are within and convert without having to individually select
each date? Please advise. Thanks!
 
D

Doug Robbins - Word MVP

The following will limit the conversion to any date that is included in the
range that is selected when it is run. Dates outside that range will not be
converted.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While
..Execute(findText:="[0-1]{1}[0-9]{1}\/[0-3]{1}[0-9]{1}\/[0-9]{1}[0-9]{1}",
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End <=
myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


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

Kim

The following will limit the conversion to any date that is included in the
range that is selected when it is run. Dates outside that range will not be
converted.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While
.Execute(findText:="[0-1]{1}[0-9]{1}\/[0-3]{1}[0-9]{1}\/[0-9]{1}[0-9]{1}",
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End <=
myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With

--
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 above works great if I only have the date highlighted. Most of the
text I need to convert is within a paragraph with running text or
table cells. Is it possible to select the paragraph/cell that the
dates are within and convert without having to individually select
each date? Please advise. Thanks!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Forgive my stupidity, but I can't seem to get the code to work. I just
get an error message that "End if without Block If." I copied the
code, and inserted _ as needed. Help!
 
G

Graham Mayor

Forgive my stupidity, but I can't seem to get the code to work. I just
get an error message that "End if without Block If." I copied the
code, and inserted _ as needed. Help!
Click to expand...

You probably broke a line or two incorrectly - in any case it can be
simplified a little more, to maintain the lines through the newsreader

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End _
<= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
D

Doug Robbins - Word MVP

With that find string, I would suggest reinstating Greg's check to ensure
that the found text is a date.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If IsDate(Selection.Range) And Selection.Range.Start >= myrange.Start
And _
Selection.Range.End <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


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

Graham Mayor said:
Forgive my stupidity, but I can't seem to get the code to work. I just
get an error message that "End if without Block If." I copied the
code, and inserted _ as needed. Help!
Click to expand...

You probably broke a line or two incorrectly - in any case it can be
simplified a little more, to maintain the lines through the newsreader

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End _
<= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Click to expand...
 
G

Graham Mayor

It's never ending ;)

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
With that find string, I would suggest reinstating Greg's check to
ensure that the found text is a date.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If IsDate(Selection.Range) And Selection.Range.Start >=
myrange.Start And _
Selection.Range.End <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With



Graham Mayor said:
Forgive my stupidity, but I can't seem to get the code to work. I
just get an error message that "End if without Block If." I copied
the code, and inserted _ as needed. Help!
Click to expand...

You probably broke a line or two incorrectly - in any case it can be
simplified a little more, to maintain the lines through the
newsreader Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}",
_ MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End
_ <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Click to expand...
Click to expand...
 
D

Doug Robbins - Word MVP

If only Jack was...

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

Graham Mayor said:
It's never ending ;)

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
With that find string, I would suggest reinstating Greg's check to
ensure that the found text is a date.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If IsDate(Selection.Range) And Selection.Range.Start >=
myrange.Start And _
Selection.Range.End <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With



Graham Mayor said:
Forgive my stupidity, but I can't seem to get the code to work. I
just get an error message that "End if without Block If." I copied
the code, and inserted _ as needed. Help!

You probably broke a line or two incorrectly - in any case it can be
simplified a little more, to maintain the lines through the
newsreader Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While .Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}",
_ MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And Selection.Range.End
_ <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mayor

Then I would be in trouble :(

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
If only Jack was...


Graham Mayor said:
It's never ending ;)

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
With that find string, I would suggest reinstating Greg's check to
ensure that the found text is a date.

Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While
.Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True If
IsDate(Selection.Range) And Selection.Range.Start >= myrange.Start And _
Selection.Range.End <= myrange.End Then
Selection.Range = Format(Selection.Range, "MMMM d, yyyy")
End If
Loop
End With



Forgive my stupidity, but I can't seem to get the code to work. I
just get an error message that "End if without Block If." I copied
the code, and inserted _ as needed. Help!

You probably broke a line or two incorrectly - in any case it can
be simplified a little more, to maintain the lines through the
newsreader Dim myrange As Range
Set myrange = Selection.Range
myrange.Find.ClearFormatting
With Selection.Find
Do While
.Execute(findText:="[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{2,4}", _
MatchWildcards:=True, Forward:=True, Wrap:=wdFindStop) = True
If Selection.Range.Start >= myrange.Start And
Selection.Range.End _ <= myrange.End Then Selection.Range =
Format(Selection.Range, "MMMM d, yyyy") End If
Loop
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change a great number of words in word files, with a macro as short as possible. 5
Loop until end 1
Select Text to End of Footnote 9
Word macro that can call cells out of Excel for a find and replace 0
CleanUpPastedText() 1
Convert date text to mm/dd/yyyy 1
VBA to look for more than 1 key word and highlights 8
count a string within a selected text 1

Top