Home |
Search |
Today's Posts |
#1
|
|||
|
|||
social security sorting
I am having problems with sorting social secuirity/tax id numbers that were
dropped into excel from several different sources. Some of the ss/tax id numbers have dashes and some do not. Some of the ss/tax id numbers are missing leading zero's. I am not able to change all of them into a text format. How can I formatt them all so they will sort properly? Thanks for any assistance out there. |
#2
|
|||
|
|||
Don't change them to text. Change them to numbers. Remove all of the dashes
with Search and Replace. Then apply the special format for Social Security numbers. On Tue, 25 Jan 2005 05:45:02 -0800, "Precious Pearl" <Precious wrote: I am having problems with sorting social secuirity/tax id numbers that were dropped into excel from several different sources. Some of the ss/tax id numbers have dashes and some do not. Some of the ss/tax id numbers are missing leading zero's. I am not able to change all of them into a text format. How can I formatt them all so they will sort properly? Thanks for any assistance out there. |
#3
|
|||
|
|||
I'm not sure what form tax id's are supposed to take, but
to format all the SSN's correctly, try: =TEXT(REPT("0",9-LEN(SUBSTITUTE(A1,"-","")))&A1,"000-00- 0000") HTH Jason Atlanta, GA -----Original Message----- I am having problems with sorting social secuirity/tax id numbers that were dropped into excel from several different sources. Some of the ss/tax id numbers have dashes and some do not. Some of the ss/tax id numbers are missing leading zero's. I am not able to change all of them into a text format. How can I formatt them all so they will sort properly? Thanks for any assistance out there. . |
#4
|
|||
|
|||
"Jason Morin" wrote: I'm not sure what form tax id's are supposed to take, but to format all the SSN's correctly, try: =TEXT(REPT("0",9-LEN(SUBSTITUTE(A1,"-","")))&A1,"000-00- 0000") HTH Jason Atlanta, GA -----Original Message----- I am having problems with sorting social secuirity/tax id numbers that were dropped into excel from several different sources. Some of the ss/tax id numbers have dashes and some do not. Some of the ss/tax id numbers are missing leading zero's. I am not able to change all of them into a text format. How can I formatt them all so they will sort properly? Thanks for any assistance out there. . |
#5
|
|||
|
|||
Thank you so much for your help. We have spent hours trying to get the
numbers to sort. Would this work as well with the tax id numbers by changing the formula to read "00-0000000"? "Jason Morin" wrote: I'm not sure what form tax id's are supposed to take, but to format all the SSN's correctly, try: =TEXT(REPT("0",9-LEN(SUBSTITUTE(A1,"-","")))&A1,"000-00- 0000") HTH Jason Atlanta, GA -----Original Message----- I am having problems with sorting social secuirity/tax id numbers that were dropped into excel from several different sources. Some of the ss/tax id numbers have dashes and some do not. Some of the ss/tax id numbers are missing leading zero's. I am not able to change all of them into a text format. How can I formatt them all so they will sort properly? Thanks for any assistance out there. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
show macro security setting in a cell | Excel Worksheet Functions | |||
How do I insert hypens in existing column representing social sec. | Excel Discussion (Misc queries) | |||
Setting macro security level to "low" permanently? | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) |