Need to change numbers with dashes to numbers with no dashes
The spreadsheet we're working has social security numbers and phone numbers
with dashes included. Need to remove dashes and not lose the leading zero in the SSN. |
Need to change numbers with dashes to numbers with no dashes
Editreplace, find - replace with leave blank, then use a custom format of
000000000 for SSN and 0000000000 for phone numbers Of course if the original are using SSN formatting then you only need to change that. Finally, if you need a physical leading zero use a help column after the dashes have been replaced and use =TEXT(A1,"000000000") copy down and then copy and paste special as values in place -- Regards, Peo Sjoblom "cheryltraining" wrote in message ... The spreadsheet we're working has social security numbers and phone numbers with dashes included. Need to remove dashes and not lose the leading zero in the SSN. |
Need to change numbers with dashes to numbers with no dashes
for the SSNs -- use this example for SSN in cell A1:
=LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4) Phone numbers would depend on the exact format. -- hope to help, cm "cheryltraining" wrote: The spreadsheet we're working has social security numbers and phone numbers with dashes included. Need to remove dashes and not lose the leading zero in the SSN. |
Need to change numbers with dashes to numbers with no dashes
Are your "numbers" text strings, or are they numbers formatted in special
ways? If stored as text, all you need to do is =SUBSTITUTE(A1,"-","") to get rid of the hyphens, and you'll still be left with a text string so won't lose the leading zeroes. If you've got SSN stored as a number and formatted to show the hyphens, then =TEXT(A1,"000000000") would give you leading zeroes padded out to 9 digits. -- David Biddulph "cheryltraining" wrote in message ... The spreadsheet we're working has social security numbers and phone numbers with dashes included. Need to remove dashes and not lose the leading zero in the SSN. |
All times are GMT +1. The time now is 08:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com