How can I sort an Excel file on e-mail address extensions?

Question

Example: I want to sort an Excel database so that all all records containing a Yahoo e-mail address are grouped together. Example: *@yahoo.com. The Excel file contains thousands of records. One column contains e-mail addresses. Can anyone direct me to a add-on or command that can do this?

Answer 1

First, make a copy of the worksheet. This way you can work on the copy instead.

Select the column of email addresses. Copy, paste to make a clone of it on the next column.

Click Data/Text to columns/delimited/
In the other editbox, enter the enclosed "@"

The domain is now nicely separated for you to sort.

2 ways to skin a cat!

Answer 2

Insert a column which will be used to enter a formula which will extract the text after the "@" sign. For purposes of this answer, let's assume your email addresses are in col. A, starting in cell A1, and you have inserted a new col. B. In B1 enter the formula:

=RIGHT(A1,LEN(A1)
-FIND("@",A1))

Note: to display correctly here, the formula is broken onto two lines. You can copy and paste it in the formula bar, backspace to eliminate the line return, and change the A1 cell references as appropriate.

Copy and paste the formula down through the last row in col. B where there is data in col. A. Now col. B will just be the domain name, e.g. yahoo.com, and you can sort on it. Afterwards, you can hide col. B, if you wish.

Explanation: the formula finds the position of the "@" in the text in cell A1, then calculates how many characters are to the right of the @ by subtracting the position of the @ from the total length (no. of characters) in A1, then extracting that number of right-most characters.