Text after a dash

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("-
",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-Pro


"Steve" wrote:

> 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

If you want the text after the first dash:
For text in A1
B1: =TRIM(RIGHT(A1,LEN(A1)-SEARCH("-",A1)))


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

XL2002, WinXP-Pro


"Steve" wrote:

> 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

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

"Ron Coderre" wrote:

> Try this:
> For text in A1
> B1: =TRIM(RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("-
> ",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-Pro
>
>
> "Steve" wrote:
>
> > 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

On Wed, 25 Jan 2006 18:11:02 -0800, "Steve" <Steve@discussions.microsoft.com>
wrote:

>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

Note that if you start with the "3rd character to the right of each dash" your
results would be:

he Penguins
arty Robbins

So I changed it to the 2nd character:

=MID(A1,FIND("-",A1)+2,255)


--ron