Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Precious Pearl
 
Posts: n/a
Default 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.
  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
Precious Pearl
 
Posts: n/a
Default



"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   Report Post  
Precious Pearl
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
show macro security setting in a cell Mark Excel Worksheet Functions 3 March 21st 05 08:53 AM
How do I insert hypens in existing column representing social sec. hard head Excel Discussion (Misc queries) 1 January 5th 05 07:47 PM
Setting macro security level to "low" permanently? Arobind Excel Discussion (Misc queries) 1 December 22nd 04 02:41 AM
sorting question Brian Excel Discussion (Misc queries) 4 November 28th 04 01:30 PM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"