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]

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 style="background: none repeat scroll 0% 0% rgb(255, 255, 255); border-collapse: collapse; border-width: 2px; border-color: rgb(204, 204, 204); font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="1"><tbody><tr><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255);"> </th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">A</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">B</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">C</th></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">1</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">john said, "I AM IN LOVE" in his latest
tale of fantasy</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">I AM IN LOVE</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">2</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">The tall guy, said, "THE NAME OF THE PLA
Y IS "BEAUTIFUL", BUT "CONFUSING"", afte
r the play.</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">THE NAME OF THE PLAY IS "BEAUTIFUL", BUT
"CONFUSING"</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">3</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td colspan="4" style="background: none repeat scroll 0% 0% rgb(153, 204, 255); padding-left: 1em;"> [Book1]Sheet3</td></tr></tbody></table>

Now that is clever, very clever . Thank you Pedro.

 

[Rick Rothstein]

Here is another formula that will extract the correct text (it's a little longer than Pedro's, but it will work)...

=MID(SUBSTITUTE(A1,""""&SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ",CHAR(1)),"""",REPT(" ",999)),999)),CHAR(1)," "),""),FIND("""",A1)+1,999)

This formula assumes the text will never be longer than 999 characters.

 

[Rick Rothstein]

And here is one more formula (still assuming a maximum text length of 999), but it is an array entered** formula...

=MID(LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:999")),1)="""",ROW(INDIRECT("1:999"))))-1),FIND("""",A1)+1,999)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Rick Rothstein]

One more (normally entered) formula... this one takes Pedro's formula as a starting point, but rearranges things a little and trades one of his FIND function calls for a LEFT function call while keeping the same total 8 overall function calls (and cuts 4 characters from the total number of characters as well)...

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

Note: This formula, like my previous submissions, assumes a maximum text length of 999 characters.

 

[VoG]

And here is one more formula (still assuming a maximum text length of 999), but it is an array entered** formula...

=MID(LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:999")),1)="""",ROW(INDIRECT("1:999"))))-1),FIND("""",A1)+1,999)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Falls off chair

 

[Rick Rothstein]

Falls off chair

I sense you are making fun of my formula Peter.

Hopefully my last formula submission will redeem myself a little.

 

[mohanyathish]

Hi guys....



A1:“what the hell “I Love You”!” – were the last words from his mouth. (Page 14/Line 5)


any formulae suggestions to get the following data....?


B1: “I Love You”
C1: were the last words from his mouth
D1: Chapter 14/column 5


thanks in advance for your help....

 

[pgc01]

Hi mohanyathish, I'm glad it helped!

Peter, thank you, but I really only tweaked your formula.

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

Rick, nice!

This means that your udf, using your formula, gets also simpler (and even simpler since the Mid() function in vba has the third parameter optional):

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

 

[Rick Rothstein]

Rick, nice!

This means that your udf, using your formula, gets also simpler (and even simpler since the Mid() function in vba has the third parameter optional):

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

"Nice!" back at you, although I cannot believe it did not occur to me to apply this Mid/Left method to my own function once it had occurred to me.