Formatting Social Security Numbers

[Elaine]

I was wondering whether there was a way I could use a macro in Word to
format a column of numbers as Social Security Numbers. We are currently
using a table and typing in SSNs without the - in the proper place.

I would like to be able to select the column and have word format it like
this 123-45-6789. I would also need to be able to determine whether there
was a number in the cell or not before it formatted. I know that cells in
Xcel can be formatted for SSNs, but I have not found anything similar in
Word. We use Word XP.

Thanks for any help in getting me started on this.

Elaine

 

[Chad Knudson]

Here's a function I wrote that does just that. It formats Tax ID numbers.
Pass in True for bIndividual to format the number as a social security
number (False formats it as a corporate tax ID number).

Public Function FormatTaxID(strCandidate As String, bIndividual As Boolean,
ByRef strTaxID As String, BlankAsValid As Boolean) As Boolean

Dim strDigits As String
Dim ch As String
Dim cDigits As Integer
Dim i As Integer
Dim iLastDigit As Integer
Dim bSuccess As Boolean

bSuccess = True

If (Len(strCandidate) > 0) Then

iLastDigit = -1
strDigits = ""

cDigits = 0

For i = 1 To Len(strCandidate)
ch = Mid(strCandidate, i, 1)
If (IsDigit(ch)) Then
cDigits = cDigits + 1
If (cDigits = 9) Then
iLastDigit = i
End If
strDigits = strDigits & ch
End If
Next

If (cDigits = 9) Then
If (bIndividual) Then
strTaxID = Mid(strDigits, 1, 3) & "-" & Mid(strDigits, 4, 2)
& "-" & Mid(strDigits, 6, 4)
Else
strTaxID = Mid(strDigits, 1, 2) & "-" & Mid(strDigits, 3, 7)
End If
Else
bSuccess = False
End If
Else
bSuccess = BlankAsValid
End If

FormatTaxID = bSuccess

End Function

 

[Andrew Savikas]

Hi Elaine

Here's how I'd format a nine-digit number as an SS#

Function FormatSSN(str As String) As Strin
str = Replace(str, "-", ""
str = Join(Array(Left(str, 3), Mid(str, 4, 2), Right(str, 4)), "-"
FormatSSN = st
End Functio

HTH
Andrew Savikas

 

[Jay Freedman]

Hi Elaine

Following on after Chad and Andrew, here's yet another way to handle
it.

Adjust the values of the TableNumber and ColumnNumber constants to
match your document, or use InputBoxes to ask for the values.

Sub SSN_format()
Const TableNumber = 1
Const ColumnNumber = 2
Dim oTbl As Table
Dim oCell As Cell
Dim oRg As Range

On Error GoTo BadCols

' if no table, bail
If ActiveDocument.Tables.Count < TableNumber Then
MsgBox "There is no table " & TableNumber, _
vbCritical + vbOKOnly, "Error"
Exit Sub
End If

Set oTbl = ActiveDocument.Tables(TableNumber)

' if not enough columns, bail
If oTbl.Columns.Count < ColumnNumber Then
MsgBox "There is no column " & ColumnNumber, _
vbCritical + vbOKOnly, "Error"
Exit Sub
End If

For Each oCell In oTbl.Columns(ColumnNumber).Cells
Set oRg = oCell.Range
' exclude end-of-cell mark
oRg.MoveEnd unit:=wdCharacter, Count:=-1

With oRg
If .Text Like "#########" Then
.Text = Format(.Text, "000-00-0000")
End If
End With
Next oCell

Exit Sub

BadCols:
If Err.Number = 5992 Then
MsgBox "Unable to process table " & _
TableNumber & " because it" & vbCr & _
"contains split or merged cells"
Else
MsgBox Err.Description, vbCritical + vbOKOnly, _
"Error " & Err.Number
End If
End Sub