CODEWITHSUNDEEP
excelexcel-formula
Oleg Vovk
Oleg Vovk

Reputation: 73

Microsoft Excel: How to extract "text with quotes "from quotation marks"

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.

Upvotes: 0

Views: 22669

Answers (3)

Cornelius
Cornelius

Reputation: 338

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)

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

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

Upvotes: 2

Ali NasserEddine
Ali NasserEddine

Reputation: 323

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.

Upvotes: 4

Related Questions