Formatting SSNs

C

Cheri

I am trying to format a column of SSNs keeping any zeros in front and
dropping the hyphen. Can someone help me with this extra formatting?
Cheri
 
K

Kevin B

You can place the following custom function in a VBA module by pressing
Alt+F11 and then clicking on Insert and selecting module
-------------------------------------------------------------------------------------------------
Function SSN(Value As Long) As String

Dim strVal As String
Dim i As Integer

strVal = CStr(Value)
i = Len(strVal)

Do Until i = 9
strVal = "0" & strVal
i = Len(strVal)
Loop

SSN = strVal

End Functio
-------------------------------------------------------------------------------------------------
Save a backup copy of your file before proceeding.

You can use this function like any other function. If your in cell B1 and
the SS# is in A1 type "=SSN(A1)" sans quotes to convert the number SS# to a
text string. You can then copy the column of formulas and paste
special/Values over the original number.

Kevin Backmann
 
D

Doug Kanter

Cheri said:
I am trying to format a column of SSNs keeping any zeros in front and
dropping the hyphen. Can someone help me with this extra formatting?
Cheri
Click to expand...

What going to be done with these numbers after they're formatted this way?
That could be a factor in how you achieve your goal.
 
C

Cheri

I understand the formula but if I put it into a cell, how do I get the SSN
into the cell - if I paste it, it removed the formula.

Cheri
 
D

dlw

you enter the ssn's in column A, you put the formula in column B, format
column A as text so the leading zeors stay.
 
D

Doug Kanter

As Kevin B mentioned in his message, you'll have to use two columns to make
this trick work. Or, you could hide the original SSNs on a second sheet in
the workbook, and put this formula (or Kevin's suggestion) in the column
where you want the jazzed up SSNs to appear. You'd have to adjust the two
suggestions so they point to the second sheet.
 
C

Cheri

I am still having difficulty with this concatenate formula. I have a
spreadsheet I need to import as a flat file. First I have to put info into
an Excel spreadsheet. If I use the concat formula, it appears in a column
that I cannot have in my spreadsheet becasue the import parameters are
already set. Column A is my SSNs. I cannot use them in the import with
hyphens and if zeros drop off. Can anyone help me to format Column A so
zeros don't drop off and hyphens go away? I can use Column H for a formula.
Please answer with lower level geek speak. Thanks
 
Top