Help with formula

[CBR1100XX Blackbird]

Hi All,
I wish to create a formula to be able to split a text string in a given
place and copy one part to another cell and the other part to a different
cell.
Please let me explain.... I have a worksheet ranging from cell A1 to A6000
containing a list of all my MP3 files stored on my PC. The format of all the
file names is for example Artist Name - Song Title eg: Madonna - Hung Up.
I wist to take all characters before the space hyphen space and copy this to
column B and all charachters after the space hyphen space and copy these to
column C.
This is what I should end up with:
A1 B1 C1
Madonna - Hung Up Madonna Hung Up

The text strings in cell A1 : A6000 are all varying lenghts and this is
where I a struggeling to get a formula to work.
I am using Excel version 2002 SP3

Many thanks
Dave

 

[CBR1100XX Blackbird]

Hi JR,
many thanks for the quick response. The formula works great on the first
cell but when I drag it to the celkls below it misses out the first wotd of
the song title as below:
Madonna - Hung Up Madonna
Hung Up
Neil Diamond - Love On The Rocks Neil Diamond On The Rocks
Aqua - Barbie Girl Aqua Girl

Any thought why it should do this ?
Thanks

 

[Jacob Skaria]

Dear "CBR1100XX Blackbird"

In B1 try
=TRIM(LEFT(A1,FIND("-",A1)-1))

and in C1 try
=TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)))

Copy that down..and enjoy

If this post helps click Yes

 

[CBR1100XX Blackbird]

Jacob,
Thanks, This works a treat !!

Dear "CBR1100XX Blackbird"

In B1 try
=TRIM(LEFT(A1,FIND("-",A1)-1))

and in C1 try
=TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)))

Copy that down..and enjoy

If this post helps click Yes

 

[Jacob Skaria]

Cheers!

If this post helps click Yes