extract a sentence from a string

[BBBryan]

Hi,
I was having trouble extracting a sentence from a string.
What I have is a Music title of a number and song name. I want to extract the song name without the artist and .MP3 after the number.

I have this example:
D4-001 Scary Monsters and Nice Sprites - Skrillex.Mp3
D4-002 D4-003 Bad Romance - Lady GaGa.Mp3
D4-003 Club Rocker - Inna & Flo Rida.mp3
D4-004 Hangover (Original Mix) - FloRida Ft. Taio Cruz.mp3
D4-004a Hangover - FloRida Ft. Taio Cruz.mp3
D4-005 Not Giving Up On Love - Armin Van Buuren & Sophie Bextor.mp3
D4-006 Out Of My Mind (Original Mix) - Bingo Players.mp3
D4-007 She Wolf (Falling To Pieces) - David Guetta Ft. Sia.mp3
D4-007a She Wolf Koaz Ft. Mb Mashup) - David Guetta Ft. Sia.mp3
D4-0008 Papi - Jennifer Lopez.mp3

I want to extract this:
Scary Monsters and Nice Sprites
Bad Romance
Club Rocker
Hangover (Original Mix)
Hangover
Not Giving Up On Love
Out Of My Mind (Original Mix)
She Wolf (Falling To Pieces)
She Wolf (Koaz Ft. Mb Mashup)
Papi

I tried the mid function
Trim(Mid([FullDescription],InStr(1,[FullDescription]," ")+1,InStr(InStr(1,[FullDescription]," ")+1,[FullDescription],"-")-InStr(1,[FullDescription]," ")))
Ut still has the space and - after the song name.

But I can't figure out how to remove the space and -.

Thanks
BBryan

 

[gemma-the-husky]

try this

use instr to find the first space
use instrrev to find the -

then use mid to strip out the title

so something like this

trim(mid(title,firstresult,secondresult-firstresult))

it will become a problem if the artist name includes a "-"

 

[BBBryan]

Hi Gemma-The-Husky,
Thanks for your responce. I am still stuck.
I am not sure what you mean.
Are you saying Start all over like this

trim(mid(title,firstresult,secondresult-firstresult))

Trim(Mid([FullDescription],InStr(1,[FullDescription]," ") - first result ***Gets me the full song name and includes .Mp3

then I want to remove the rest of the sentence from the space before the -.
Trim(Mid([FullDescription],InStr(2,[FullDescription],"-") - second result ***I am not sure how to get the second - syntax
Then - I guess after I know how to do this I can Minus the 2 parts?

Sorry still need help.

Thanks BBryan

The trouble I am have is I am not sure of the syntax to put this all together.

 

[gemma-the-husky]

here you go

replace test with your field name

title = Trim(Mid(test, InStr(1, test, " "), InStrRev(test, "-") - InStr(1, test, " ")))


as I say, it won't work if there are "-" characters in the artist name. I can't think of how you could resolve it, if there was.

 

[BBBryan]

Thanks Gemma-The-Husky,
Sorry - I guess I didn't understand the first responce what you were meaning , But Now I see want you were meaning. I will try not to put any dashes in.


Thanks again.

BBryan