Possible to sort part of a cell?

[helios02]

I have a column with email addresses in the format: [email protected]. Is it possible to sort these in order based on the last name?

[Tom Ogilvy]

If you make a dummy column and extract the last name

assume the example is in A1. Then in B1 enter

=MID(A1,FIND(".",A1)+1,FIND("@",A1)-FIND(".",A1)-1)

And drag fill down the column. Now sort columns A and B with B as the key
column.

--
Regards,
Tom Ogilvy


"helios02" <[email protected]> wrote in
message news:[email protected]...
>
> I have a column with email addresses in the format:
> [email protected]. Is it possible to sort these in
> order based on the last name?
>
>
> --
> helios02
> ------------------------------------------------------------------------
> helios02's Profile:
http://www.excelforum.com/member.php...o&userid=29791
> View this thread: http://www.excelforum.com/showthread...hreadid=494969
>

[Don Guillett]

try putting this in a helper column
=MID(E6,FIND(".",E6)+1,FIND("@",E6)-FIND(".",E6)-1)
--
Don Guillett
SalesAid Software
[email protected]
"helios02" <[email protected]> wrote in
message news:[email protected]...
>
> I have a column with email addresses in the format:
> [email protected]. Is it possible to sort these in
> order based on the last name?
>
>
> --
> helios02
> ------------------------------------------------------------------------
> helios02's Profile:
> http://www.excelforum.com/member.php...o&userid=29791
> View this thread: http://www.excelforum.com/showthread...hreadid=494969
>

[helios02]

Thanks, it works.

[Don Guillett]

glad to help but Tom was faster.

--
Don Guillett
SalesAid Software
[email protected]
"helios02" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks, it works.
>
>
> --
> helios02
> ------------------------------------------------------------------------
> helios02's Profile:
> http://www.excelforum.com/member.php...o&userid=29791
> View this thread: http://www.excelforum.com/showthread...hreadid=494969
>

[bulina2k]

Hi there!

Old thread but very usefull...
My question is: is there a VBA way to sort a table after just a part of a column?
I have a ("B") column like

900E
13241E
133E
13244E
14E

Normally if I try to sort the result would be something like..
13241E
13244E
133E
14E
900E

I would like to be able to sort my table using just the numeric value:
14E
900E
133E
...
...
without using additional/dummy column. Can it be done?
Thank you !

[shg]

I would like to be able to sort my table using just the numeric value:
14E
900E
133E
...
...
without using additional/dummy column. Can it be done?
Thank you !

Why does 900 appear before 133?

[bulina2k]

I'm sorry, you're right.. just a typo :P
so:
14E
133E
900E
13241E
13244E

[bulina2k]

I think I've found a way but again I need your help.
In order to sort the table using the numbers in column "B" I'll use Range.Replace to erase that "E" at the end of the number.
Then I'll sort the table with DataOption1:=xlSortTextAsNumbers sort option and then (here I need your help) I will add that "E" tail back to each cell in column "B".
Is there a way to use Range.Replace (or any other function) to add a char to the values in a range without using a loop ?

Thanks again!

Please Login or Register  to view this content.

[jindon]

Is there a way to use Range.Replace (or any other function) to add a char to the values in a range without using a loop ?

Please Login or Register  to view this content.

[bulina2k]

Thank you very much jindon, that works perfect for a fixed range (B2:B650).
Is there a way to use it for let's say [B2:B & lr] ?
lr being the last used row.

[jindon]

If use of Variable, it should be like

Please Login or Register  to view this content.

[bulina2k]

Arigatou gozaimasu jindon.

[jindon]

Dō itashimashite.