software

I have a list of songs, but the name of the song and artist are all in the
same cell. I'd like to be able to sort by artist.

Is there a formula that I could enter in B1 ,B2 , etc.
that could text everything starting with the 3rd character to the right of
each dash (-) ?
(A1)
Earth Angel - The Penguins
(A2)
El Paso - Marty Robbins

so that the B column result would be
The Penguins
Marty Robbins
Note: The 13 characters in these samples are obviously just a coincidence,
and other cells will have different number of characters.

Thanks,

Steve

Try this:
For text in A1
B1: =TRIM(RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;-
quot;,A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )))

That pulls the text to right of the last occurrence of the dash.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Stevequot; wrote:

gt; I have a list of songs, but the name of the song and artist are all in the
gt; same cell. I'd like to be able to sort by artist.
gt;
gt; Is there a formula that I could enter in B1 ,B2 , etc.
gt; that could text everything starting with the 3rd character to the right of
gt; each dash (-) ?
gt; (A1)
gt; Earth Angel - The Penguins
gt; (A2)
gt; El Paso - Marty Robbins
gt;
gt; so that the B column result would be
gt; The Penguins
gt; Marty Robbins
gt; Note: The 13 characters in these samples are obviously just a coincidence,
gt; and other cells will have different number of characters.
gt;
gt; Thanks,
gt;
gt; Steve

If you want the text after the first dash:
For text in A1
B1: =TRIM(RIGHT(A1,LEN(A1)-SEARCH(quot;-quot;,A1)))***********
Regards,
Ron

XL2002, WinXP-Proquot;Stevequot; wrote:

gt; I have a list of songs, but the name of the song and artist are all in the
gt; same cell. I'd like to be able to sort by artist.
gt;
gt; Is there a formula that I could enter in B1 ,B2 , etc.
gt; that could text everything starting with the 3rd character to the right of
gt; each dash (-) ?
gt; (A1)
gt; Earth Angel - The Penguins
gt; (A2)
gt; El Paso - Marty Robbins
gt;
gt; so that the B column result would be
gt; The Penguins
gt; Marty Robbins
gt; Note: The 13 characters in these samples are obviously just a coincidence,
gt; and other cells will have different number of characters.
gt;
gt; Thanks,
gt;
gt; Steve

Perfect. Thanks much. This one actually worked a little better than the other
one, because if there was a dash in the song title, the result of the other
one was not correct. This one seems to work completely.

Thanks again,

Steve

quot;Ron Coderrequot; wrote:

gt; Try this:
gt; For text in A1
gt; B1: =TRIM(RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(quot;-
gt; quot;,A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )))
gt;
gt; That pulls the text to right of the last occurrence of the dash.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Stevequot; wrote:
gt;
gt; gt; I have a list of songs, but the name of the song and artist are all in the
gt; gt; same cell. I'd like to be able to sort by artist.
gt; gt;
gt; gt; Is there a formula that I could enter in B1 ,B2 , etc.
gt; gt; that could text everything starting with the 3rd character to the right of
gt; gt; each dash (-) ?
gt; gt; (A1)
gt; gt; Earth Angel - The Penguins
gt; gt; (A2)
gt; gt; El Paso - Marty Robbins
gt; gt;
gt; gt; so that the B column result would be
gt; gt; The Penguins
gt; gt; Marty Robbins
gt; gt; Note: The 13 characters in these samples are obviously just a coincidence,
gt; gt; and other cells will have different number of characters.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Steve

On Wed, 25 Jan 2006 18:11:02 -0800, quot;Stevequot; gt;
wrote:

gt;I have a list of songs, but the name of the song and artist are all in the
gt;same cell. I'd like to be able to sort by artist.
gt;
gt;Is there a formula that I could enter in B1 ,B2 , etc.
gt;that could text everything starting with the 3rd character to the right of
gt;each dash (-) ?
gt;(A1)
gt;Earth Angel - The Penguins
gt;(A2)
gt;El Paso - Marty Robbins
gt;
gt;so that the B column result would be
gt;The Penguins
gt;Marty Robbins
gt;Note: The 13 characters in these samples are obviously just a coincidence,
gt;and other cells will have different number of characters.
gt;
gt;Thanks,
gt;
gt;Steve

Note that if you start with the quot;3rd character to the right of each dashquot; your
results would be:

he Penguins
arty Robbins

So I changed it to the 2nd character:

=MID(A1,FIND(quot;-quot;,A1) 2,255)--ron