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

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.

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

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,"-","")


--
Regards,
Dave


"LCDawn" wrote:

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.

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

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
--
Gary''s Student


"LCDawn" wrote:

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.

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

On Thu, 22 Dec 2005 07:45:03 -0800, LCDawn
wrote:

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

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

You're Awesome, Dave...!

Thanks so much.

Kindest Regards,

Dawn

"David Billigmeier" wrote:

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,"-","")


--
Regards,
Dave


"LCDawn" wrote:

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.