[Amy Comer]

I'm having trouble with Microsoft word and excels Find and Replace
functions. Any help would be really appreciated.
Basically i have an excel file that is thousands of columns down..
which I need to change the formatting!!

I am trying to take digits that start out looking like:

0314-T210557

And turn them into:

14-Mar-09 21:05:57


So far, (In Microsoft word) I have used Find and Replace to get rid of
the dash and the T. I have also used find and replace and put a tab in
between the 0314 and the 210557.. (so that when I put it back into
excel, they are in separate columns).

However, I cannot figure out how to get the 210557 looking like a
time.
What I want to do is use Find and replace to put : in between the
21:05:57...
In the find box, I can use ^# .. but it will not let me put those in
the replace box. How can I do this???
So
Find: ^#^#^#^#^#^#
Replace: ^#^#:^#^#:^#^#
But this keeps giving me an error message that I cant put ^# into the
replace box. Any ideas?




Also, Does anyone have any advice how how I can turn 0314.. into 14-
mar-09?

Thank you so much for any help you can give!!!

[Graham Mayor]

It is not possible to do this easily with replace. The issue concerns
changing the date number to text. You can change it to 14-03-09 21:05:57
easily enough (and then I guess Excel would reformat that) by replacing
([0-9]{2})([0-9]{2})-T([0-9]{2})([0-9]{2})([0-9]{2})
with
\2-\1-09 \3:\4:\5
with the wildcard option set in one pass, but to put the date in Words would
need 12 separate replacements.

If you want to change the date to use text you are going to need a macro
e.g.

Sub ReformatDateText()
Dim oRng As Range
Dim sDate As String
Dim sDay As String
Dim sMonth As String
Dim sYear As String
Dim sTime As String
With Selection
.HomeKey wdStory
With .Find
.ClearFormatting
.Replacement.Text = ""
.Replacement.ClearFormatting
Do While .Execute(findText:="[0-9]{4}-T[0-9]{6}", _
MatchWildcards:=True)
Set oRng = Selection.Range
sMonth = Left(oRng.Text, 2)
Select Case sMonth
Case "01": sMonth = "Jan"
Case "02": sMonth = "Feb"
Case "03": sMonth = "Mar"
Case "04": sMonth = "Apr"
Case "05": sMonth = "May"
Case "06": sMonth = "Jun"
Case "07": sMonth = "Jul"
Case "08": sMonth = "Aug"
Case "09": sMonth = "Sep"
Case "10": sMonth = "Oct"
Case "11": sMonth = "Nov"
Case "12": sMonth = "Dec"
End Select
sDay = Mid(oRng.Text, 3, 2)
sTime = Mid(oRng.Text, 7, 2)
sTime = sTime & Chr(58) & Mid(oRng.Text, 9, 2)
sTime = sTime & Chr(58) & Right(oRng.Text, 2)
sDate = sDay & Chr(45) & sMonth & Chr(45)
sDate = sDate & "09" & Chr(32) & sTime
oRng.Text = sDate
Loop
End With
End With
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Amy Comer" wrote in message
...
I'm having trouble with Microsoft word and excels Find and Replace
functions. Any help would be really appreciated.
Basically i have an excel file that is thousands of columns down..
which I need to change the formatting!!

I am trying to take digits that start out looking like:

0314-T210557

And turn them into:

14-Mar-09 21:05:57


So far, (In Microsoft word) I have used Find and Replace to get rid of
the dash and the T. I have also used find and replace and put a tab in
between the 0314 and the 210557.. (so that when I put it back into
excel, they are in separate columns).

However, I cannot figure out how to get the 210557 looking like a
time.
What I want to do is use Find and replace to put : in between the
21:05:57...
In the find box, I can use ^# .. but it will not let me put those in
the replace box. How can I do this???
So
Find: ^#^#^#^#^#^#
Replace: ^#^#:^#^#:^#^#
But this keeps giving me an error message that I cant put ^# into the
replace box. Any ideas?




Also, Does anyone have any advice how how I can turn 0314.. into 14-
mar-09?

Thank you so much for any help you can give!!!