Working with social security numbers

  • Thread starter Thread starter Andre F
  • Start date Start date
A

Andre F

I am trying to remove the hyphen from between social security numbers;I'm
able to do so using the 'text to column' function, but I am having a problem
with those numbers that are preceded by 0's eg : 001-23-0077 the function is
dropping the zero's and returning 1-23-77....
 
And you want to separate the SSN into three separate columns?

You can still use data|text to columns, but make sure you specify that each
column is Text (not General).
 
I am trying to remove the hyphen from between social security numbers;I'm
able to do so using the 'text to column' function, but I am having a problem
with those numbers that are preceded by 0's eg : 001-23-0077 the function is
dropping the zero's and returning 1-23-77....
Click to expand...

Assuming the ssn's are entered as text, use the Replace command to
replace - with nothing.

Be sure to select the range with just the ssn's before using this
command or you'll get rid of all the dashes in your worksheet.

Bill
 
Other options...

Select the range to fix
Use Edit|Replace
what: - (hyphen)
with: (leave blank)
replace all

This will drop the leading 0's, too.

But you could use a custom format of: 000000000
so the data looks nice.

You could also use a formula in another cell (or column of cells):
=substitute(a1,"-","")
(and drag down)

This will keep the leading 0's and the values will be text.
 
Back
Top