Strippinng characters out of excel

  • Thread starter Thread starter Pii via OfficeKB.com
  • Start date Start date
P

Pii via OfficeKB.com

I have a bunch of social security numbers for employees in the following
format. We are exporting this report to the tax office and they do not
accept hyphens in the ss numbers. We have over 1000 numbers and I don't
want to do it by hand. Thanks in advance

123-456-7890

Need it to look like
1234567890
 
go to Edit -->replace
find: -
replace : leave this blank

This will replace all the - with blank space
 
Do a global replacement in the cells in the column(s) and replace "-" by "",
no quotes.

Regards

Trevor
 
It could be that the numbers are just formatted as ss numbers, you may only
need to change the formatting to general, if you need to remove the hyphens
just use edit replace, fine what - replace with leave blank

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Hi Pii

Assume SS Numbers In Column A

In Column B =Left(A1,3)
In Column C =Mid(A1,5,3)
In Column D =Right(A1,4)
In Column E =B1&C1&D1

Copy down all formulae in Columns B,C,D & E
Then CopyPasteSpecial Values Of Column E Into Column F And Delete Columns B to E

Mathew

I have a bunch of social security numbers for employees in the following
format. We are exporting this report to the tax office and they do not
accept hyphens in the ss numbers. We have over 1000 numbers and I don't
want to do it by hand. Thanks in advance

123-456-7890

Need it to look like
1234567890
 
FYI, after the global Find/Replace (as that's the easiest way to do it) you
should know that Excel has a (Special) Format for SSN.

Ctrl + 1 | Number (tab) | Special (left) | Social Security Number (right)

It's much like a custom fomat of ..

000"-"00"-"0000
 
Back
Top