How to grab a text value from second quotation in Excel?-CodePudding

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

B2: "Description":"BLUE CAR"
C2: "Description":"RED CAR"
D2: "Description":"PINK CAR"

I found a formula that can grab the second string in quotes which is: BLUE CAR RED CAR PINK CAR

=MID(B2,FIND("""",B2) 1,FIND("""",B2,FIND("""",B2) 1)-FIND("""",B2)-1)

But this gives me:

Description
Description
Description

Is there a way to grab the second quotation string?

CodePudding user response：

If your value was in A1 ...

=SUBSTITUTE(SUBSTITUTE(INDEX(TEXTSPLIT(A1,":"), 1, 3), """", ""), ",", "")

... however, will depend on your version of Excel.

It also assumes that there are no additional commas in the string apart from the last one. You can fix that though if need be.

CodePudding user response：

Perhaps you can try using TEXTAFTER()

• Formula used in cell C2

=SUBSTITUTE(TEXTAFTER(B2,":"),"""",)

Edit:

• Formula used in cell D2

=SUBSTITUTE(TEXTAFTER(TEXTBEFORE(B2,","),": ",2),"""",)

Using MAP()

• Formula used in cell C2

=MAP(B2:B4,LAMBDA(m,SUBSTITUTE(TEXTAFTER(TEXTBEFORE(m,","),": ",2),"""",)))

Note: Formulas will work with MS365 current channel and the last two edits are based on OP's edits.