How do I format a social security number to have no dashes?

[Guest]

I have a column in Excel with social security numbers and i need to make the
formatting so the number has no dashes in it. So for instance 111-11-1111,
should look like 11111111. Is there a quick way to format my column to look
like this??

Thanks

Kim

 

[Guest]

Hi Kim,

Copy this function and paste as a new Module (press Alt+F11)

use a function =digitsonly(a2)
hth
regards from Brazil
Marcelo
********************************************
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
********************************************



"kim" escreveu:

 

[Guest]

Is your SIN a 9 digit number that has special formatting to include the
dashes or is it text that already physically includes the dashes? If its the
1st case just format as text.

 

[Guest]

=LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)

Try this formula if it is all text.

 

[Guest]

Use a customer format. Format > Cells, on the number tab select Custom in
the category list at the left. In the text box labeled 'Type' enter the
format: 000000000, then click OK.

 

[Dave Peterson]

If the hyphens are part of the number format (not part of the value), then
Select the range
format|cells|Number tab
custom category
type: 000000000
in the "Type:" box

If the hyphens are really part of the data (liked they were typed in), then
Select the range
edit|replace
what: - (hyphen)
with: (leave blank)
replace all

And apply that same custom format.