Sorting on Portions of the Contents of a Cell

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

Additional query words: 1.5 2.0 2.00 2.01 2.1 2.10 2.20 2.21 3.0 5.0

Keywords :
Version :
Platform :
Issue type :