Working with social security numbers
 

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....

Working with social security numbers
 

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).



Andre F wrote:

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....

--

Dave Peterson

Working with social security numbers
 

On 2/24/2010 2:03 PM, Andre F wrote:
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....

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

Working with social security numbers
 

have you tired using Find and Replace (Ctrl + H)
--
HTH...

Jim Thomlinson


"Andre F" wrote:

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....

Working with social security numbers
 

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.

Andre F wrote:

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....

--

Dave Peterson