Solved: Leading zero's for a SSN #

[debauch]

Hello,
I am using a very picky excel sheet, that requires the leading zero's, but the cell cannot be formatted. It is currently in general text, and this is why it cuts the zero out.

Is there any other way to correct this ? I tried a single quatation, which kept the zero there, but in the function line, the quotation was still there.

[Zack Barresse]

Hi there,

It's not a quotation mark, it's a single apostrophe (one single tick mark). :)

[debauch]

Hi there,

It's not a quotation mark, it's a single apostrophe (one single tick mark). :)

That's what I meant. It still shows up when I look in function bar. The reason I need this is, the sheet it being uploaded into a system that chokes on things, but the SSN when in general format, take the zero out. We need the zero's though.



ThankS!

[Jacob Hilderbrand]

If the data is in Col A then run this macro. It will add in the leading zeros. I assumed you did not want the - either.


Option Explicit

Sub Macro1()

Dim i As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
Range("A" & i).Value = Format(Val(Range("A" & i).Value), "000000000")
Next

End Sub

[Zack Barresse]

Then you need to format the cells as text, not as numerical, as Excel will still hold the Value of no leading zero's. A format on the other hand, is just a mask that shows a value in a different way.

[debauch]

Thanks.