software

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

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 = quot;0quot; amp; strVal
i = Len(strVal)
Loop

SSN = strVal

End Function
-------------------------------------------------------------------------------------------------
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 quot;=SSN(A1)quot; 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 Backmannquot;Cheriquot; wrote:

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


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

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.

=CONCATENATE(LEFT(A1,3),MID(A1,5,2),RIGHT(A1,4))quot;Doug Kanterquot; wrote:

gt;
gt; quot;Cheriquot; gt; wrote in message
gt; ...
gt; gt;I am trying to format a column of SSNs keeping any zeros in front and
gt; gt; dropping the hyphen. Can someone help me with this extra formatting?
gt; gt; Cheri
gt;
gt; What going to be done with these numbers after they're formatted this way?
gt; That could be a factor in how you achieve your goal.
gt;
gt;
gt;

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

quot;dlwquot; wrote:

gt;
gt; =CONCATENATE(LEFT(A1,3),MID(A1,5,2),RIGHT(A1,4))
gt;
gt;
gt; quot;Doug Kanterquot; wrote:
gt;
gt; gt;
gt; gt; quot;Cheriquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I am trying to format a column of SSNs keeping any zeros in front and
gt; gt; gt; dropping the hyphen. Can someone help me with this extra formatting?
gt; gt; gt; Cheri
gt; gt;
gt; gt; What going to be done with these numbers after they're formatted this way?
gt; gt; That could be a factor in how you achieve your goal.
gt; gt;
gt; gt;
gt; gt;

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.

quot;Cheriquot; wrote:

gt; I understand the formula but if I put it into a cell, how do I get the SSN
gt; into the cell - if I paste it, it removed the formula.
gt;
gt; Cheri
gt;
gt; quot;dlwquot; wrote:
gt;
gt; gt;
gt; gt; =CONCATENATE(LEFT(A1,3),MID(A1,5,2),RIGHT(A1,4))
gt; gt;
gt; gt;
gt; gt; quot;Doug Kanterquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Cheriquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt;I am trying to format a column of SSNs keeping any zeros in front and
gt; gt; gt; gt; dropping the hyphen. Can someone help me with this extra formatting?
gt; gt; gt; gt; Cheri
gt; gt; gt;
gt; gt; gt; What going to be done with these numbers after they're formatted this way?
gt; gt; gt; That could be a factor in how you achieve your goal.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

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.

quot;Cheriquot; gt; wrote in message
news
gt;I understand the formula but if I put it into a cell, how do I get the SSN
gt; into the cell - if I paste it, it removed the formula.
gt;
gt; Cheri
gt;
gt; quot;dlwquot; wrote:
gt;
gt;gt;
gt;gt; =CONCATENATE(LEFT(A1,3),MID(A1,5,2),RIGHT(A1,4))
gt;gt;
gt;gt;
gt;gt; quot;Doug Kanterquot; wrote:
gt;gt;
gt;gt; gt;
gt;gt; gt; quot;Cheriquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt;I am trying to format a column of SSNs keeping any zeros in front and
gt;gt; gt; gt; dropping the hyphen. Can someone help me with this extra formatting?
gt;gt; gt; gt; Cheri
gt;gt; gt;
gt;gt; gt; What going to be done with these numbers after they're formatted this
gt;gt; gt; way?
gt;gt; gt; That could be a factor in how you achieve your goal.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
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

quot;Cheriquot; wrote:

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