Calling all excel gurus..

B

boerenooi

Well-Known Member
Joined
Nov 27, 2009
Messages
185
Reaction score
1
I have an excel database of email addresses, but I would like to alphabetically order it, but only everything after the @ (order according to the domain name), does anyone know how I can do this?
 
look for the @ with find function then use mid to get everything after the @ and sort according to this...

[email protected] (first colum)
=find("@",A1,1)+1 (second colum)
=mid(a1,b1,99) (third colum)

then just order on the third colum....

you could use one colum something like ( =mid(a1,find("@",a1,1)+1,99)
 
Last edited:
use the mid function after you find the @ symbol, then sort.

+1 herbertk, same time post, but yours is more descriptive! you could put that all together

=mid(A1(find("@",A1,1),99)
 
Last edited:
IRG said:
use the mid function after you find the @ symbol, then sort.

+1 herbertk, same time post, but yours is more descriptive! you could put that all together

=mid(A1(find("@",A1,1),99) ??
Click to expand...

wait one mistake =mid(A1(find("@",A1,1)+1,99) otherwise all addresses have the @
 
herbertk said:
wait one mistake =mid(A1(find("@",A1,1)+1,99) otherwise all addresses have the @
Click to expand...

Thats ok tho, cause all the addresses will have the "@", thus the sort will look at the 2nd letter for sorting further, so it will still be alphabetically....
 
Im lost? lol. I've put =FIND("@",A1:A5688,1) into my second column, and what I got was 12. lol. I'm obvioulsy doing something wrong.
 
boerenooi said:
Im lost? lol. I've put =FIND("@",A1:A5688,1) into my second column, and what I got was 12. lol. I'm obvioulsy doing something wrong.
Click to expand...

u must refer to one cell at a time (A1 is the first cell you are trying to do) the number 12 is telling you that it has found the @ symbol at letter no 12 of the full address.

once u have the formula, u copy and paste it thru the rest of your sheet. But remember the 2nd part of the formula (mid function) to actually get the domain name of the address.

Summarize:

PART A = find where the @ symbol is formula = find("@",A1,1)+1 this will look at Cell A1, starting at the first letter of the address, it will count where the "@" symbol is and add 1
PART B = Get the domain name formula = mid(A1,find("@",A1,1)+1),99) this will look at A1 from the 13th letter to 99 letters long
 
Last edited:
IRG said:
u must refer to one cell at a time (A1 is the first cell you are trying to do) the number 12 is telling you that it has found the @ symbol at letter no 12 of the full address.
Click to expand...

Ok, but ive got like 5688 email addresses. If I only say A1, will it sort all of them?
 
IRG said:
u must refer to one cell at a time (A1 is the first cell you are trying to do) the number 12 is telling you that it has found the @ symbol at letter no 12 of the full address.
Click to expand...

Ok, but ive got like 5688 email addresses. If I only say A1, will it sort all of them?
 
IRG said:
u must refer to one cell at a time (A1 is the first cell you are trying to do) the number 12 is telling you that it has found the @ symbol at letter no 12 of the full address.

once u have the formula, u copy and paste it thru the rest of your sheet. But remember the 2nd part of the formula (mid function) to actually get the domain name of the address.

Summarize:

PART A = find where the @ symbol is formula = find("@",A1,1)+1 this will look at Cell A1, starting at the first letter of the address, it will count where the "@" symbol is and add 1
PART B = Get the domain name formula = mid(A1,find("@",A1,1)+1),99) this will look at A1 from the 13th letter to 99 letters long
Click to expand...

Ok, when I do the first part, It gives me numbers, is this right?
When I do the second part, it tells me that I've entered to few arguments for this function.
:(
 
you really only need to do PART B, copy the formula exactly as is. The only thing u need to change if necessary is the referring cell number (if your address is not in A1). Part A was for you to understand what we are trying to do.

Copy this as is:

=mid(A1,find("@",A1,1)+1,99)

and paste it in your B1 column (if available)

I did typo in the first example (sorry) to many closing parenthesis (brackets)
 
IRG said:
So u got it working then ... no probs, sharing is caring! and if u are a chick, sorry for calling you a dude :)
Click to expand...

ha ha yeah one assumes most here are dudes... but boerenooi is def a chick or a dude with issues ha ha
 
Yeah, didnt really look at the username that closely, till i wracked my brain a bit about "nooi" and remembered it had something to do with girl... my afrikaans isnt what it used to be since moving to Natal :)

for if you just want the domain name....

=MID(A1,FIND("@",A1,1)+1,FIND(".",A1,FIND(".",A1,(FIND("@",A1,1))))-(FIND("@",A1,1))-1)
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X