How do I change a social security number to a number series?

[Guest]

I want to take a spreadsheet that has numbers entered as social security
numbers and change the display to be a number series. Some of the numbers
begin with 0. I'd like to have all the dashes removed as well.

 

[Guest]

So do you want to keep the 0's appended on the front? If so, use:

=TEXT(SUBSTITUTE(A1,"-",""),"000000000")

If not (you want the 0's in front of the number to be taken out) use:

=--SUBSTITUTE(A1,"-","")

 

[Guest]

Let's say the numbers are text in the form:

'123-456-7890

Select the cells (columns or rows) and pull-down
Edit > Find and enter the - then replace with nothing

The leading apostrophe will insure that leading zeros are retained

 

[Ron Rosenfeld]

I want to take a spreadsheet that has numbers entered as social security
numbers and change the display to be a number series. Some of the numbers
begin with 0. I'd like to have all the dashes removed as well.

Assuming the values are TEXT and not formatted numbers, then:

=SUBSTITUTE(A1,"-","")

will return the string, with the leading zeros.

If you do not want the leading zeros, then

=--SUBSTITUTE(A20,"-","")

will convert the value into a number, and you can format it as you wish.

You could also use FIND/REPLACE to replace the "-"'s with nothing.




--ron

 

[Guest]

You're Awesome, Dave...!

Thanks so much.

Kindest Regards,

Dawn