Excel is the spreadsheet component of the Microsoft Office Desktop Productivity Suite. While commonly used for mathematical analysis of data, you can also use it to store small amounts of data. Excel has the ability to sort data such as telephone numbers, but they must be formatted consistently. Variations between individual phone numbers in presence of parentheses, added black spaces or different separator characters will result in an improper sort order.
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.
- "Excel 2007: The Missing Manual;" Matthew McDonald; 2006
- Photo Credit Stockbyte/Stockbyte/Getty Images