Number and date formatting

dmarie123 · #1 01-15-2013, 09:16 AM

Newbie problems...

Hi all, I've been scouring the web for two things and I'm coming up empty. If this has already been asked I apologize and if someone could point me to the link I'd appreciate it.
I want to change the format of a date in a word document to a "long date" e.g., January 15, 2013 and in that same document convert an SSN w/o dashes to one with dashes. Both the dates and SSNs will vary from document to document.

Date Issue
In the documents I'm given there are two date ranges (which will be different in every document) given:

1. "between 01/15/2010-06/12/2011"

and

2. "of 01/15/2010-06/12/2011"

Both sets of dates would need to read "January 15, 2010" and/or "June 12, 2011". However, for the first example the hypen should be changed to "and" and in the second example the hyphen should be changed to "through".

SSN Issue
Throughout the same documents there are SSNs without dashes. They appear as either SSN: ######### OR SSN #########. There are other numbers in the document which are 9 digit numerics so I need the search parameters to include the "SSN" and "SSN: ".

Is there a way to insert the dashes with a macro? 123456789 to 123-45-6789? I would think I could search for "#########" and change it to "###-##-####" but my google searching was fruitless.

I probably won't be able to repay all you VBA masters anytime soon but I'm not above cookie bribery

.
Thank you in advance for your time!

macropod · #2 01-29-2013, 09:12 PM

Hi dmarie,

You could use a macro like the following for both problems (the second one doesn't really require a macro, but it can be easily incorporated into the one that is required for the first problem).

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim StrTxt As String
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    'Fix SSNs
    .Text = "([0-9]{3})([0-9]{2})([0-9]{4})"
    .Replacement.Text = "\1-\2-\3"
    .Execute Replace:=wdReplaceAll
    'Fix Date ranges
    .Text = "[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}-[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}"
    .Replacement.Text = ""
    .Execute
  End With
  Do While .Find.Found
    StrTxt = Format(Trim(Split(.Text, "-")(0)), "MMMM D, YYYY")
    Select Case Trim(LCase(.Words.First.Previous.Previous.Words.First))
      Case "between": StrTxt = StrTxt & " and "
      Case "from": StrTxt = StrTxt & " to "
      Case "of": StrTxt = StrTxt & " through "
    End Select
    StrTxt = StrTxt & Format(Trim(Split(.Text, "-")(1)), "MMMM D, YYYY")
    .Text = StrTxt
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

You'll note that I've included a test for date ranges prefixed by 'from' - just to show how easily additional parameters can be accomodated.

dmarie123 · #3 01-31-2013, 09:49 AM

Thanks Paul! I appreciate you responding to me and I saw you helped a bunch of other people around the same time. I'm so grateful there are people willing to share their knowledge.

The macro worked like a charm, thank you so much. I have a few questions though because I found additional issues once I ran it.

1. Isn't .Text = "([0-9]{2})([0-9]{7}) looking for a 9 digit number? I have another ID number in the document that’s 10 digits that I don’t want to touch but now it gets a hyphen. How do I make the code above exclusive to 9 digit numbers?

2. Also, for the 10 digit number, how do I add text afterwards? It wouldn’t be the same parameters as the SSN number. Just a find “SSID: 1234567890” and replace with “SSID: 1234567890 (Status: Approved )”. The numbers will be different in every document which is why I want to use a macro. Right now we spend about an hour editing. Since starting to use macros it’s down to 4 hotkeys…

3. For legal reasons we have to track all changes to these documents but when I turn them on the track changes breaks the macro and instead of inserting the “-“ after the second digit it sends the hyphen to the end. I utilized your macro to adjust our student IDs which are formatted similarly to a tax id, e.g., 12-3456789 and thought I was doing something wrong when I added the code below but it was because of the track changes being on.
.Text = "([0-9]{2})([0-9]{7})"
.Replacement.Text = "\1-\2"

4. As I said, for legal reasons we have to track all changes it breaks the following macro (thank you Greg Maxey) also. If I add “ActiveDocument.TrackRevisions = True” then I get runtime error 4198. Previously when I got this error it was because there were already content controls in the document but this is a new word doc with nothing on it but words haha. Please help, I’m stumped.

Code:

Sub ReplaceWithConentControl() 
    Dim oRng As Word.Range 
    Dim strFind() As String 
    Dim strInput As String 
    Dim i As Long 
    Dim oCC As ContentControl 
    strFind() = Split("this provider|the provider", "|") 
    strInput = InputBox("Enter provider's last name:", "Input") 
    For i = 0 To UBound(strFind) 
        Set oRng = ActiveDocument.Range 
        With oRng.Find 
            .ClearFormatting 
            .Replacement.ClearFormatting 
            .Text = strFind(i) 
            .Forward = True 
            .Wrap = wdFindContinue 
            .Format = False 
            .MatchCase = False 
            .MatchWholeWord = False 
            .MatchWildcards = False 
            .MatchSoundsLike = False 
            .MatchAllWordForms = False 
            While .Execute 
                Set oCC = ActiveDocument.ContentControls.Add(wdContentContro lText, oRng) 
                With oCC 
                     '"Comments" property
                    .XMLMapping.SetMapping "ns1:coreProperties[1]/ns0:description[1]", , ActiveDocument.CustomXMLParts(1) 
                    .Range.Text = strInput 
                    .Title = "Name" 
                End With 
            Wend 
        End With 
    Next 
End Sub

As always, any help is appreciated. Thank you for your time!
Donna Marie

macropod · #4 01-31-2013, 03:12 PM

Quote:

Originally Posted by dmarie123

1. Isn't .Text = "([0-9]{2})([0-9]{7}) looking for a 9 digit number? I have another ID number in the document that’s 10 digits that I don’t want to touch but now it gets a hyphen. How do I make the code above exclusive to 9 digit numbers?

To exclude longer number strings and non-SSN strings, try using:
.Text = "(SSN[: ]{1,2}[0-9]{3})([0-9]{2})([0-9]{4})"

Quote:

2. Also, for the 10 digit number, how do I add text afterwards? It wouldn’t be the same parameters as the SSN number. Just a find “SSID: 1234567890” and replace with “SSID: 1234567890 (Status: Approved )”.

Try adding:

Code:

'Fix SSIDs
    .Text = "SSID[: ]{1,2}[0-9]{10}"
    .Replacement.Text = "^& (Status: Approved )"
    .Execute Replace:=wdReplaceAll

before:
'Fix Date ranges

Quote:

3. For legal reasons we have to track all changes to these documents but when I turn them on the track changes breaks the macro and instead of inserting the “-“ after the second digit it sends the hyphen to the end. I utilized your macro to adjust our student IDs which are formatted similarly to a tax id, e.g., 12-3456789 and thought I was doing something wrong when I added the code below but it was because of the track changes being on.
.Text = "([0-9]{2})([0-9]{7})"
.Replacement.Text = "\1-\2"

Track changes does create problems! What you might try with the SSNs, for example, is:

Code:

    'Fix SSNs
    ActiveDocument.TrackRevisions = False
    .Text = "(SSN[: ]{1,2}[0-9]{3})([0-9]{2})([0-9]{4})"
    .Replacement.Text = "\1-\2-\3"
    .Execute Replace:=wdReplaceAll
    ActiveDocument.TrackRevisions = True
    .Text = "[0-9]{3}-[0-9]{2}-[0-9]{4}"
    .Replacement.Text = "^&"
    .Execute Replace:=wdReplaceAll

This will give you a correctly formatted output and, whilst the tracked change won't exactly represent that only the hyphens were added, at least it will show that something was changed. If this approach is acceptable, we can extend it to the other scenarios.

dmarie123 · #5 02-15-2013, 10:21 AM

I sincerely appreciate all your help!! I'll mark this as solved

macropod · #6 02-28-2013, 03:10 PM

Hi Donna,

Try the following refinement. It tracks only the actual changes made:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim TrkStatus As Boolean, StrTxt As String, Rng As Range
With ActiveDocument
  TrkStatus = .TrackRevisions
  .TrackRevisions = True
  Set Rng = .Range(0, 0)
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Wrap = wdFindStop
      .Format = False
      .MatchWildcards = True
    End With
 
    'Fix SSNs
    .Start = Rng.Start
    With .Find
      .Text = "SSN[: ]{1,2}[0-9]{9}>"
      .Replacement.Text = ""
      .Execute
    End With
    Do While .Find.Found
      .Start = .End - 7
      .End = .End - 3
      .InsertAfter "-"
      .InsertBefore "-"
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
 
    'Fix SSIDs
    .Start = Rng.Start
    With .Find
      .Text = "SSID[: ]{1,2}[0-9]{10}>"
      .Execute
    End With
    Do While .Find.Found
      .Collapse wdCollapseEnd
      .Text = " (Status: Approved)"
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
 
    'Fix Date Ranges
    .Start = Rng.Start
    With .Find
      .Text = "[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}-[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}"
      .Execute
    End With
    Do While .Find.Found
      Select Case Trim(LCase(.Words.First.Previous.Previous.Words.First))
        Case "between": StrTxt = " and "
        Case "from": StrTxt = " to "
        Case "of": StrTxt = " through "
      End Select
      .Start = .Start + InStr(.Text, "-") - 1
      .End = .Start + 1
      .Duplicate.Text = StrTxt
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
 
  End With
  .TrackRevisions = TrkStatus
End With
Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date formatting	kjxavier	Excel	18	08-23-2011 08:21 AM
Date formatting	kjxavier	Excel	6	08-12-2011 05:46 AM
Date formatting	kjxavier	Excel	0	08-10-2011 08:17 AM
Phone number formatting	Mark Micallef	Outlook	1	08-04-2010 02:38 PM
Help needed using the serial number date with sumifs - whole office is stumped	FraserKitchell	Excel	3	01-06-2010 12:24 PM