Sorting on Portions of the Contents of a Cell

 Last reviewed: September 12, 1995
Article ID: Q62719
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
  • Microsoft Excel for the Macintosh, versions 1.5, 2.2, 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SUMMARY

Microsoft Excel cannot sort information based on a portion of a cell's contents; however, if the information in the cell is text, you can use text functions to extract the desired sorting criteria to an adjacent column, then sort the adjacent column.

MORE INFORMATION

For example, consider the following cells: 

   -|--------A--------|------B------|
   1|123 123 4432 2222|             |
   2|432 564 3254 6666|             |
   3|325 754 0074 1111|             |
   4|677 431 8944 3333|             |
To sort by only the last four digits of the cells, use the following formula in column B to extract only those last characters 

   =RIGHT(cell-to-left,4)
where "cell-to-left" refers to A1, A2, A3, and A4.

This formula produces the following: 

   -|--------A--------|-----B------|
   1|123 123 4432 2222|    2222    |
   2|432 564 3254 6666|    6666    |
   3|325 754 0074 1111|    1111    |
   4|677 431 8944 3333|    3333    |
Select both columns of data, and sort with B1 as the first sort key. (In version 5.0, it is not necessary to select both columns of data.) Column A is now sorted by the last four digits. The following results: 

   -|--------A--------|-----B------|
   1|325 754 0074 1111|    1111    |
   2|123 123 4432 2222|    2222    |
   3|677 431 8944 3333|    3333    |
   4|432 564 3254 6666|    6666    |
Middle digits can be found using the MID() function. For example, to sort by the third set of numbers in the above data, use the following formula, which extracts four characters beginning with the ninth digit: 

   =MID(cell-to-left,9,4)

REFERENCES

"Function Reference," version 4.0, pages 274 and 363-364

"Function Reference," version 3.0, pages 151-152 and 200

KBCategory: kbusage
KBSubcategory:

Additional reference words: 1.5 1.50 2.0 2.00 2.01 2.1 2.10 2.2 2.20
2.21 3.0 3.00 4.0 4.00 5.0 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.