Remove formatting from SSN
 

I exported some data from an Access database. The Social Security Numbers
are in this format: 123-45-6789. I would like to change them all to
123456789.

I've tried creating a Custom Format and formatting the cells, but nothing
changed. Tried setting the format of a new column, then cutting and pasting
or Paste Special into the new cells, but it keeps the old formatting. Is
there an answer?

Try this in a "helper" column:

=TEXT(A1,"000000000")

To retain your leading zeroes.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Claire View" wrote in message
...
I exported some data from an Access database. The Social Security Numbers
are in this format: 123-45-6789. I would like to change them all to
123456789.

I've tried creating a Custom Format and formatting the cells, but nothing
changed. Tried setting the format of a new column, then cutting and
pasting
or Paste Special into the new cells, but it keeps the old formatting. Is
there an answer?

Select the column and
edit|replace
what: - (dash)
with: (leave blank)
replace all

Then apply your custom format of 000000000

Or use a helper column of cells filled with formulas like:

=text(--substitute(a1,"-",""),"000000000")



Claire View wrote:

I exported some data from an Access database. The Social Security Numbers
are in this format: 123-45-6789. I would like to change them all to
123456789.

I've tried creating a Custom Format and formatting the cells, but nothing
changed. Tried setting the format of a new column, then cutting and pasting
or Paste Special into the new cells, but it keeps the old formatting. Is
there an answer?

--

Dave Peterson

Hi Claire,
I can't imagine why you would want to change them from text to
numbers, but since you can't change the number format and have it
take right away you have text. You can fix by using Ctrl+H and
replacing the "-" with nothing. It will be text and you want numbers
so you will then have do something like add and empty cell to each.

Select and copy an empty cell
Select the column of ssno then Edit, paste special, Add

Okay RagDyer's requires a helper column and you have several
extra steps to put things into order without the extra column.

Dave Peterson's 1st solution will work -- thought it would result in text,
but would suggest you apply the formatting first -- that way if you
had the column as Text instead of General it would still work.
His second solution is same as RagDyer's.

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Claire View" wrote in message ...
I exported some data from an Access database. The Social Security Numbers
are in this format: 123-45-6789. I would like to change them all to
123456789.

I've tried creating a Custom Format and formatting the cells, but nothing
changed. Tried setting the format of a new column, then cutting and pasting
or Paste Special into the new cells, but it keeps the old formatting. Is
there an answer?