TAKING DASHES OUT OF SOCIALS AND ZIP CODES
Posted by JIM BYRNE on August 08, 2001 6:23 AM
Hi have 10,000 records that I need to take the dashes out of social security numbers and zip codes with the 4 digit extentions. Any idea how to do that? Thanks
Posted by Ian Bartlett on August 08, 2001 6:32 AM
Jim,
Assume your number with a dash is in cell D4. This should find & remove the dashes:
=REPLACE(D4,FIND("-",D4),1,"")
Paste it beside your original numbers all the way down. Convert them to plain numbers with copy / paste special - values only.
HTH,
Ian
Posted by Barrie Davidson on August 08, 2001 6:33 AM
Hi Jim, I'm from Canada so I have no idea of the format for your social security numbers and your zip codes. Can you post samples of each?
Barrie
Posted by Dwight on August 08, 2001 6:38 AM
Not sure if it will work efficiently for 10,000 records, but for a single cell I just did a find & replace (start by hitting Ctrl+F) to find the dashes ("find what?" is -) and replace them with nothing ("replace with" leave blank) and then select "replace all". Resulting number is in scientific notation but easily reformatted ("format, cells, custom, 0")
Hope this works for you
Posted by Mark W. on August 08, 2001 6:38 AM
No need to be concerned with the position of the
hyphens. Just use...
=SUBSTITUTE(A1,"-","")
