advanced sorting in excel? 1

[muz]

I have a list of adresses ex: xyx@abc.com and want to sort them by company name. What's the easiest / best way to do this? I do not know access, but will learn if this is the best.

thanks

 

[woja]

Try this in Excel:
[ol][li]Enter your addresses in column A, one per cell[/li]
[li]In column B use the formula [tt]=LEFT(A1,FIND("@",A1)-1)[/tt] and copy it down to populate column B with the name part of the addresses.[/li]
[li]In column C use the formula [tt]=RIGHT(A1,LEN(A1)-FIND("@",A1))[/tt] and copy it down to populate column C with the company names[/li]
[li]Then sort the sheet on Column C then Column B[/li]
[/ol]
This assumes you know a bit about Excel. If you need more info, then let me know.

Hope this helps.


[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[KenWright]

Or just copy Col A to Col B as well, then select Col B and do Data / Text To Columns / Delimited / tick other and enter an @ sign, hit the 'do not import' option on the first column of text and then hit OK.

Now select all data and sort on Col B

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[woja]

Yeah, Ken.

Us programmers always seem to go for the complicated solution...

...worth a star

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[muz]

thanks allot, one more question if I may..
the email addresses are in the form of
xyx@abc.com <xyx@abc.com> is there a way to truncate or just make them xyx@abc.com

thanks again you've helped allot

 

[woja]

Now, that's a formula:
[tt]=LEFT(A1,FIND(&quot;<&quot;,A1)-1)[/tt]
Put that in column B then coopy it down. Then you can use Ken's method to get the text into company any name (or, if you like, you could use formulae, but that's a bit of an overkill unless you want a dynamic list).

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[muz]

It's easier to beg forgiveness than ask permission,

you must be married and know the rules

thanks, is there something to put in the last formula to recognize the address if the format if some of the addresses are only xyx@abc.com and not the full
xyx@abc.com <xyx@abc.com>

thanks again

 

[woja]

try:

=IF(FIND(&quot;<&quot;,A1)=0,A1,LEFT(A1,FIND(&quot;<&quot;,A1)-1))

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[DrBowes]

Or just use Replace in the Edit menu to move all occurences of < and >, after selecting the cells if you don't want others affected.

 

[KenWright]

Data / text to Columns / Delimited / Space as delimiter, select 'do not import' on second column.

And yes!!!

Regards
Ken...............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[muz]

thanks again,
tried

=IF(FIND(&quot;<&quot;,A1)=0,A1,LEFT(A1,FIND(&quot;<&quot;,A1)-1))

doesn't sem to work,

thanks again