Number and date formatting

dmarie123 · #1 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 · #2 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 · #3 02-15-2013, 10:21 AM

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

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