Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
I have thousands of SSNs that imported to my Excel 2002 document without
formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
Try this:
1. copy your ssn's to a new column. 2. in an un-used cell enter the number 1 3. copy the cell 4. select the new column of ssn's 5. do an edit paste/special with multiply checked this will convert the column to true numbers 6. re-format the new column to SSN format. -- Gary's Student "ClaireView" wrote: I have thousands of SSNs that imported to my Excel 2002 document without formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
Why not just format the original column? If you really want the dashes (i.e., as part if the text entry vs a number entry) then parse out the number & add the dashes: =LEFT(A1,3)&"-"&MID(A1,3,4)&"-"&RIGHT(A1,4) ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=527850 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
Why can't you select the entire column containing the numbers, select
FormatCellsNumber tab. Select Special from the Category list, select SSN format and click OK. All of the unformatted numbers should change to the SSN foprmat. "ClaireView" wrote: I have thousands of SSNs that imported to my Excel 2002 document without formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
I don't know why I didn't think of multiplying by one when adding zero didn't
work, but it did the trick! Thanks for all your help! "ClaireView" wrote: I have thousands of SSNs that imported to my Excel 2002 document without formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
Adding 0 or multiplying by one will do the same thing, my guess is that the
cell you copied had a different format -- Regards, Peo Sjoblom http://nwexcelsolutions.com "ClaireView" wrote in message ... I don't know why I didn't think of multiplying by one when adding zero didn't work, but it did the trick! Thanks for all your help! "ClaireView" wrote: I have thousands of SSNs that imported to my Excel 2002 document without formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying SSNs to new format
I have the opposite problem. I have thousands of SSNs in the 123-45-6789
format which I want as a number in the format 123456789. I have tried custom formatting(000000000), changing text to columns and then joining the three separate cells into one. EX: 012-01-0123 becomes 121123, even with each cell having custom formatting applied. As ClairView wrote: Please, what am I doing wrong?!! "ClaireView" wrote: I have thousands of SSNs that imported to my Excel 2002 document without formatting. I would like to change them from "123456789" to "123-45-6789." I researched this, and other postings here instructed me to create a new column, format the new cells to "Special / Social Security Number," then copy the unformatted cells using Paste Special and choose Add. This doesn't work. The format still says it's in SSN format, but the numbers are still 123456789. Please, what am I doing wrong?!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a format that has been conditionally formated | Excel Discussion (Misc queries) | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
Copying a date format | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |