Remove all text outside of " "

[stonypaul]

Greetings,

If I have a string such as the following in cell A1 what code would remove everything outside the speech marks, inclusive of the speech marks?

The Black Eyed Peas : "Meet Me Halfway"

 

[Aladin Akyurek]

Greetings,

If I have a string such as the following in cell A1 what code would remove everything outside the speech marks, inclusive of the speech marks?

The Black Eyed Peas : "Meet Me Halfway"

Try...

=SUBSTITUTE(REPLACE(A2,1,FIND(CHAR(34),A2),""),CHAR(34),"")

if

Meet Me Halfway

is the desired result.

 

[T. Valko]

Greetings,

If I have a string such as the following in cell A1 what code would remove everything outside the speech marks, inclusive of the speech marks?

The Black Eyed Peas : "Meet Me Halfway"

Try this...

=SUBSTITUTE(MID(A2,FIND(": ",A2)+3,50),RIGHT(A2),"")

 

[stonypaul]

Thanks guys - I'll try both

 

[Scott Huish]

=trim(replace(substitute(a1,"""",rept(" ",254)),1,254,""))

 

[T. Valko]

Try this...

=SUBSTITUTE(MID(A2,FIND(": ",A2)+3,50),RIGHT(A2),"")

Improvement...

1 less function call and a few keystrokes shorter.

=SUBSTITUTE(MID(A2,FIND(": ",A2)+3,50),"""","")

 

[Scott Huish]

http://wiki.answers.com/Q/What_is_the_longest_song_title

=REPLACE(LEFT(A1,LEN(A1)-1),1,FIND("""",A1),"")

 

[T. Valko]

Improvement...

1 less function call and a few keystrokes shorter.

=SUBSTITUTE(MID(A2,FIND(": ",A2)+3,50),"""","")

Tweak...

Just in case you might have an entry like:

Pink Floyd : "Several Species Of Small Furry Animals Gathered Together In A Cave And Grooving With A Pict"

=SUBSTITUTE(MID(A2,FIND(": ",A2)+3,255),"""","")

 

[Scott Huish]

 

[T. Valko]

I didn't look at your link until I had already made that reply!

I wanted to see if they had that song listed.