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

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

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

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:

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

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

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.

"kim" wrote:

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

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

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

Try this formula if it is all text.

"kim" wrote:

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

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

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.

"kim" wrote:

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

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

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.

kim wrote:

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

--

Dave Peterson