0

I need to extract text containing quotes from quotation marks. Example, TRADE HOUSE "LLC"MEXICAN APPLES" THROUGH TESCO. I need to have in an adjacent cell "LLC"MEXICAN APPLES".

I tried 

=MID(A1, SEARCH("""",A1, SEARCH("""",A1)-SEARCH("""", A1))

It did not work. I cannot understand how to set the length of the text to be extracted. The length of the text varies.

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Oleg Vovk
  • 57
  • 2
  • 2
  • 9
  • Plese describe all the cases, the logic behind. What if you have abc"LLC""MEXICAN APPLES"xyz? Should it be "LLC""MEXICAN APPLES" or "LLC"MEXICAN APPLES"? What about abc"LLC"""MEXICAN APPLES"xyz? – zaptask Apr 07 '16 at 08:11
  • It is a good question. I have a column with companies' names in every cell. And the names vary, they may be abc "llc"apple" xyz, may be abc "apple" xyz, may be abc "apple"; abc "llc""apple" xyz. The main idea is to extract text with all the quotes from outer quotation marks. – Oleg Vovk Apr 07 '16 at 08:24

3 Answers3

4

This should work fine:

=MID(A1,SEARCH("""",A1),SEARCH("^",SUBSTITUTE(A1,"""","^",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))))-LEN(LEFT(A1,SEARCH("""",A1)-1)))

You can change the character "^" to any other unusual character.

Hope this helps.

Ali NasserEddine
  • 313
  • 2
  • 6
2

Try this for your string in cell A1

=MID(A1,FIND("""",A1),FIND("@",SUBSTITUTE(A1,"""","@",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))))-FIND("""",A1))&""""

Mostly borrowed from here: Excel: last character/string match in a string

This presumes that your strings don't have an @ symbol in them. If they do, use something different in the formula

Community
  • 1
  • 1
CallumDA
  • 12,025
  • 6
  • 30
  • 52
1

Find first quotes in cell B1:

=SEARCH(CHAR(34);A1)

Find next quotes in cell C1:

=SEARCH(CHAR(34);A1;B1+1)

Formula in cell D1:

=MID(A1;B1+1;C1-B1-1)
Cornelius
  • 341
  • 5
  • 18