Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Strippinng characters out of excel
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 |
#2
|
|||
|
|||
go to Edit --replace
find: - replace : leave this blank This will replace all the - with blank space "Pii via OfficeKB.com" wrote in message ... 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 |
#3
|
|||
|
|||
Do a global replacement in the cells in the column(s) and replace "-" by "",
no quotes. Regards Trevor "Pii via OfficeKB.com" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Pii via OfficeKB.com" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Pii via OfficeKB.com" wrote in message ... 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 |
#6
|
|||
|
|||
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 -- Regards, Zack Barresse, aka firefytr "Pii via OfficeKB.com" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Access -> Excel: How to export fields > 255 characters | Excel Discussion (Misc queries) | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
Find/Replace carriage return & line feed characters in Excel. | Excel Worksheet Functions |