CODEWITHSUNDEEP
excelexcel-formulaformula
Rojo
Rojo

Reputation: 41

Extract multiple values in quotations from the same string of text

I would like to create a formula to grab all text within a string of text /sentence of a field. For example, I have the following text

Set custom property 'text1' with value '4' for Nexus 2000 Series Fabric Extender 2248TP 'DNS Name' at 'Location'.

I found a formula that can grab the first string in quotes which is 

=MID(A1,FIND("'",A1)+1,FIND("'",A1,FIND("'",A1)+1)-FIND("'",A1)-1)

However, it only extracts the first word in quotes in the sentence/string of text and I need to extract all four strings in quotes. I am hoping to place each word in quotes in a separate column.

Upvotes: 4

Views: 1908

Answers (1)

user4039065
user4039065

Reputation:

With your original string in A1 use this in B1 then fill right;

=TRIM(MID(SUBSTITUTE(CHAR(39)&$A1, CHAR(39), REPT(" ", LEN($A1))), (COLUMN(A:A))*2*LEN($A1), LEN($A1)))

enter image description here

Upvotes: 1

Related Questions