I need to convert SSN to number currently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

On Feb 22, 1:41 pm, "Deb" wrote:
I need toconvertSSNtonumbercurrently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

I found a posting from a couple of years ago and it works, but now I
need to add 0 to SSN where 0's are in fromt.
ex: 001111111 or 022222222

thanks again

If all SSN's are same length to start with you can custom format as 000000000(9
zeros) or enter this in an adjacent or helper column.

=IF(LEN(A1)<9,"0"&A1,A1) copy down.

Assumes column A has the edited SSN's

Format all cells as text.

Guess it depends on whether or not you use the data for calculations.

If you want them to be numbers there would be no reason to add back the leading
zero.


Gord

On 22 Feb 2007 11:52:53 -0800, "Deb" wrote:

On Feb 22, 1:41 pm, "Deb" wrote:
I need toconvertSSNtonumbercurrently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

I found a posting from a couple of years ago and it works, but now I
need to add 0 to SSN where 0's are in fromt.
ex: 001111111 or 022222222

thanks again

EditReplace

What: -

With: nothing

Watch out for leading zeros that can get dropped using this method.

i.e. 011-01-0111 wil lose the first zero and become 11010111


Gord Dibben MS Excel MVP

On 22 Feb 2007 11:41:29 -0800, "Deb" wrote:

I need to convert SSN to number currently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

Try using Edit/Replace.
In the Replace what enter a -
In the with leave it blank
~Kristin

"Deb" wrote:

I need to convert SSN to number currently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

Thanks for your comments but I think I need to clarify.

Currently by cell does not include leading zeros but I need the
leading zeros.

Current Cell 111111111
Update to 011111111

or

Current Cell 1111111
Update to 001111111

Thanks again

On Feb 22, 2:45 pm, "Deb" wrote:
Thanks for your comments but I think I need to clarify.

Currently by cell does not include leading zeros but I need the
leading zeros.

Current Cell 111111111
Update to 011111111

or

Current Cell 1111111
Update to 001111111

Thanks again

=REPT(0,9-LEN(A1))&A1 if you want text, or just format the cell to 000000000
if you want to leave as a number.
--
David Biddulph

"Deb" wrote in message
ps.com...
Thanks for your comments but I think I need to clarify.

Currently by cell does not include leading zeros but I need the
leading zeros.

Current Cell 111111111
Update to 011111111

or

Current Cell 1111111
Update to 001111111

Thanks again