CODEWITHSUNDEEP
excelexcel-formula
user3461502
user3461502

Reputation: 323

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

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?

enter image description here

Upvotes: 0

Views: 100

Answers (3)

Manoj
Manoj

Reputation: 461

Here another one with basic approach..

=MID(A1:A3,FIND(""":""",A1:A3)+3,LEN(A1:A3)-(FIND(""":""",A1:A3)+3))

enter image description here

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27338

Perhaps you can try using TEXTAFTER()

enter image description here

• Formula used in cell C2

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

Edit:

enter image description here

• 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.

Upvotes: 2

Skin
Skin

Reputation: 11207

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.

enter image description here

Upvotes: 1

Related Questions