Sorting Identically Formatted Telephone Numbers

• Double-click on the "File" icon to open the spreadsheet and verify that all phone numbers are formatted identically. As an example, numbers shown as "(503) 555-1212" and "(503)555-1212" are formatted differently because of the space after the area code. Excel would treat the space as any other character, and the sort order would appear incorrect.
  
  Select a range of values containing all telephone numbers, plus any assorted columns you wish to sort with the phone numbers. Select "Data" from the top pull-down menu, then choose "Sort" from the pull-down menu or ribbon. Choose "A-Z" or "Z-A" depending on the sort order you desire.
  
  

Manually Reformatting Inconsistent Data

• Consider a spreadsheet with a column of the following phone number entries: "(212) 555-1212," "303 5551212" and "(404)555-1212." An Excel sort would place the record beginning with "303" last, because parentheses are considered lower in value than the "3". A similar sorting problem would result form the space after the area code.
  
  To avoid such problems, all phone numbers must be in the exact same format, with consistent use of parentheses, spaces, and dashes or other separator characters.
  
  Manually edit the telephone numbers so that each phone number is in an identical format such as "(212) 555-1212." Sort the data and the results will be as expected.
  
  

Sorting Without Reformatting

• Excel has the ability to extract characters into a new field which allows sorting without changing the original data. Consider the entry "717)555-1212." Extracting the numbers into a separate field would eliminate all sorting issues.
  
  Consider an example with a phone number in cell A1, with a newly compiled cells (W1 through Z1) that are available for sorting. Cell A1 contains the following phone number entry: "(717)555-1212." Type the following into cell W1: "=mid(a1,2,3)" and cell W1 will now contain "717".
  
  Type the follwoing into cell X1: "=mid(a1,6,3)", and cell W2 will now contain "555".
  
  Type the following into cell Y1: "=mid(a1,10,4)", and cell Y1 will now contain "212".
  
  Type the following into cell Z1: "=concatenate (w1,x1,y1)", and cell Z1 will now contain "7175551212".
  
  Copy or modify this formula to other rows as needed and sort all data based on column Z.