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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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,"-","")
 
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
 
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.
Click to expand...


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
 
Back
Top