sort text from the right-to-left

  • Thread starter Thread starter Santistas
  • Start date Start date
S

Santistas

Hi,

I need to sort several text data by their suffix, I mean, the characters
from the right-to-left order.



Any help?
 
I think you need to give some examples of the data.

I have 600 e-mails addresses in a column.
I have to classify them by country (@yy.zz) or if they are commercial
(.com) or not commercial (.org).

To do this, they should be classified from right-to-left and, after sort,
look something like this:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
....

Thanks for any help

Santistas
 
Hi,

Sorry to insist, but I have to do an urgent report about this.

I need to sort several text data by their suffix, I mean, the characters
from the right-to-left order.

I have 600 e-mails addresses in a column.
I have to classify them:
by country (@yy.zz)
or if they are commercial (.com)
or if they are not commercial (.org).

To do this, they after sort, they should look something like this:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
...

Thanks for any help

Santistas
 
Try "Text To Columns" *first*.

With your data list in column A, select the data, then:
<Data> <TextToColumns> <Delimited> <Next>
Check "Other" and type in the @ sign, then <Next>

In the "Destination" box, enter B:B, then <Finish>

This should give you:
Column A - your original data,
Column B - all data that was in front of the @,
Column C - all data that followed the @.

Now select *all* the three columns, and choose to sort on column C first,
then on column B.

You should now have column A sorted as you wish, and be able to delete
column B and C.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Santistas said:
I think you need to give some examples of the data.
Click to expand...

I have 600 e-mails addresses in a column.
I have to classify them by country (@yy.zz) or if they are commercial
(.com) or not commercial (.org).

To do this, they should be classified from right-to-left and, after sort,
look something like this:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
....

Thanks for any help

Santistas

------------
 
If your data is in A1:A600, add a couple of helper columns (I used B and C) and
put this in B1 and drag down.

=MID(A1,SEARCH("@",A1)+1,255)

Then in C1, put this formula based on a Harlan Grove post:

=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="."),
ROW(INDIRECT("1:"&LEN(A1))))+1,255)

(all one cell).

In column B, you'll see:

bb.com
bbc.com
bcc.com
dd.org

In column C, you'll see:

com
com
com
org
org
za
zb
zc


Then sort by your data by column C (primary) and column B (secondary) and column
A (tertiary).
 
Back
Top