Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
number format | Excel Discussion (Misc queries) | |||
Number format for Combobox | Excel Discussion (Misc queries) | |||
Convert Social Security text to number? | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) |