Got a string of data in a cell and wanting to pull some data out of it

[Jaffabfc]

Hi,

So in cell G2 i have a string of data and i am wanting to pull bits out of it.
The cell shows
"{"options": [{ "name" : "size", "value" : ""},{ "name" : "ruling", "value" : ""},{ "name" : "colour", "value" : "Blue"},{ "name" : "pages", "value" : ""},{ "name" : "packsize", "value" : ""},{ "name" : "personalisation", "value" : ""}]}"

And in J1 i have Size in K1 i have Ruling L1 Colour etc etc.
in J2/K2/L2 i would want the value that is showing above as "" for when i drag the formulas down

So if the size in the string above had the value of 123 instead of "" i would want it to show in cell K2 123.

i havent a clue where to start with this one can someone help

thanks

 

[Jaffabfc]

Ok that is good, formula works. Make sure y don’t have any data next to j2.

So clear j2,k2,l2 etc

ok, i have done that and it brings up "value" as the text

 

[Cubist]

i put this in H2 and have the sheet looking exactly how it is above and i copy the formula and it brings up #N/A

Your string is probably different than what you posted above. Can you show exactly what's on the sheet?

 

[JEC]

That is correct right? Showing up the value

 

[Cubist]

That is correct right? Showing up the value

Your formula is showing the literal string "value".

 

[Jaffabfc]

this is what it is showing

 

[Jaffabfc]

View attachment 114184
this is what it is showing

i am wanting it for colour for example to show blue

 

[Cubist]

i am wanting it for colour for example to show blue

My formula didn't work because your header capitalization needs to match what's in the string. Try:

=TRIM(TEXTBEFORE(TEXTAFTER(SUBSTITUTE($G2,CHAR(34),""),LOWER(H$1)&", value :"),"}"))

 

[Jaffabfc]

My formula didn't work because your header capitalization needs to match what's in the string. Try:

=TRIM(TEXTBEFORE(TEXTAFTER(SUBSTITUTE($G2,CHAR(34),""),LOWER(H$1)&", value :"),"}"))

Brilliant, i forgot i had changed a column to help the other suggestion and not changed it back.

Thats worked thanks

 

[Cubist]

Brilliant, i forgot i had changed a column to help the other suggestion and not changed it back.

Thats worked thanks

You're welcome. Glad we can help.