computer geek question

the_learninator wrote on 3/9/2005, 12:35 PM

this questions doesn't have to do with video editing directly. it's an excel question and I hope someone can help me. I've been trying to get this done for like an hour now with no luck.

i have a list of my clients emails in alphabetical order descending based on the first character but I want them to be in alphabetical order + order by domain name for example:

instead of having:

johndoe1@yahoo.com
johnydoe1@hotmail.com
koolerguy1@aol.com
koolguy13@yahoo.com
zenzo@aol.com

I want it to be:

koolerguy1@aol.com
zenzo@aol.com
johnydoe1@hotmail.com
johndoe1@yahoo.com
koolguy13@yahoo.com

See how it's alphabetical by name and domain aol, then hotmail, then yahoo

If anyone can figure out how to solve this problem I will be forever greatful!

Comments

SonyRyanS wrote on 3/9/2005, 12:58 PM

Real Quick: I deleted your other two threads with this question, figured they were done by mistake (back in browser to edit something and re-submit perhaps?).

Also, why not just split the e-mail into two columns... ColumnA = handle; ColumnB = domain. Then sort by ColumnB then ColumnA in the Sort function (so it's by domain, then alpha by handle in the domains).

-Ryan

Orcatek wrote on 3/9/2005, 12:59 PM

Assuming value in col A*

Put following in B* and then sort on B*

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

The above formula extracts all text past the @ character.

kentwolf wrote on 3/9/2005, 1:11 PM

Another way that I would use:

User name in one column
Domain in another column, Concatanate the colums together with the @ symbol.
Cell A:1 = Name
Cell B:1 = Domain
Cell C:1= "=CONCATENATE(A1,"@",B1)"
Yields: Name@domain

You can then sort by either the user name (A) or the domain name (B).

I virtually live in Excel and that is how I would do it.

the_learninator wrote on 3/9/2005, 5:10 PM

thanks Orac! it worked! I would of never got it without your help.

hey wolf....i think the problem with your method is how would I get name in 1 column A, domain in column B wihout having to cut and paste @domain.com to each B for 2300 emails?

or is there a formula to extract a variable of @*.*

if there is I would love to know it! Thanks

PS: i took a look @ trail of the dragon! that was tight man...what type of cameras did you use......it was nice color and camera placement. how many cameras did it take....or did you just remount the same one?

amendegw wrote on 3/9/2005, 5:17 PM

If you want to extract the name, use the following formula:

=LEFT($A1,FIND("@",$A1)-1)

...Jerry

kentwolf wrote on 3/9/2005, 5:29 PM

>>...how would I get ...domain in column B wihout having to cut and paste @domain.com to each B for 2300 emails?

Example: You could put the actual domain at say, cell E:1, then paste Cell C:1= "=CONCATENATE(A1,"@",$E$1)" Notice the "$" at the column and row reference. This is an "absolute" cell reference. As you paste the rows sequentially, or the range C:1 through C: 2300 all at once, it would fill in as:

C:1= "=CONCATENATE(A1,"@",$E$1)
C:2= "=CONCATENATE(A2,"@",$E$1)
C:3= "=CONCATENATE(A3,"@",$E$1)
C:4= "=CONCATENATE(A4,"@",$E$1)
C:5= "=CONCATENATE(A5,"@",$E$1)

...$E$1 always referencing the (same) domain.

...and on we could go. There are other ways too. But, hey, if you have it working, great! Nice thing about Excel is there is often more than one way to do the same thing. :)

Also, as stated above by amendegw, =LEFT($A1,FIND("@",$A1)-1) used in conjunction with =RIGHT($A1,FIND("@",$A1)+1) could also prove useful.

Also, you can copy the cell formula, either of the above LEFT/RIGHT statements, then right-click, select "paste values" in a new cell to do a complete extraction of the needed info...