How to extract data betwee to quotes..?

[mohanyathish]

i have the following data in A1..

john said, "I AM IN LOVE" in his latest tale of fantasy.

i need this data in B1...

I AM IN LOVE

Sometimes it so happens that I have data like....

The tall guy, said, "THE NAME OF THE PLAY IS "BEAUTIFUL", BUT "CONFUSING"", after the play.

what i need is...

THE NAME OF THE PLAY IS "BEAUTIFUL", BUT "CONFUSING"

can anybody suggest a formula to extract data between quotes..?
(The first " and the last ")

thanks in advance.....

-------------------------------

what i know is a drop....what i dont know is an ocean....

 

[VoG]

Try

=MID(A1,FIND(CHAR(34),A1)+1,FIND(CHAR(34),SUBSTITUTE(A1,CHAR(34),"~",1))-FIND(CHAR(34),A1)-1)

Edit: I didn't see your edit before posting. My formula will only work with your first example.

 

[mohanyathish]

that is not working.....

please note that i changed the question....

thanks for the quick response...

 

[Rick Rothstein]

Can you use a VB user defined function (UDF)?

Function GetQuote(S As String) As String
  GetQuote = Mid(S, InStr(S, """") + 1, InStrRev(S, """") - InStr(S, """") - 1)
End Function

If you are not familiar with UDFs, you would install it by pressing ALT+F11 from any worksheet. This will open the VB editor.... once there, click Insert/Module on its menu bar and copy/paste the above code into the code window that just opened up. That's it. Go back to your worksheet and enter this into a cell (assuming A1 contains your text)...

=GetQuote(A1)

 

[mohanyathish]

I am ok fine with UDFs....

but the suggested UDF doesnt seem to work....

could you suggest any other solution..?

 

[pgc01]

Hi

Same as Peter's, just adapted to account for a case like the second example:

=MID(A1,FIND("""",A1)+1,FIND("|",SUBSTITUTE(A1,"""","|",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))))-FIND("""",A1)-1)


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">john said, "I AM IN LOVE" in his latest <br>tale of fantasy</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">I AM IN LOVE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">The tall guy, said, "THE NAME OF THE PLA<br>Y IS "BEAUTIFUL", BUT "CONFUSING"", afte<br>r the play.</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">THE NAME OF THE PLAY IS "BEAUTIFUL", BUT<br> "CONFUSING"</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

 

[pgc01]

... and Rick's udf works fine for the examples you posted.

 

[Rick Rothstein]

I am ok fine with UDFs....

but the suggested UDF doesnt seem to work....

could you suggest any other solution..?

Did you install it where I suggested? Did you change the cell reference in the formula? The code was tested before I posted it and it worked fine for the examples you posted. Does it not work for any of your text? If it works for some, but not all, please post the text it is not working for.

 

[mohanyathish]

thanks for the quick response....

the formula works perfectly......

so does the UDF.......(my mistake.....sorry Rick Rothstein...)

Thanks to both of you.....

 

[pgc01]

I'm glad all is ok. Thanks for the feedback.