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

 

[JEC]

This in J2

=LET(sp,TEXTSPLIT(G2,": "),TEXTAFTER(FILTER(sp,ISNUMBER(SEARCH(", ",sp))),", "))

 

[Cubist]

Try

Book1
	G	H	I	J	K	L	M
1		size	ruling	colour	pages	packsize	personalisation
2	"{"options": [{ "name" : "size", "value" : ""},{ "name" : "ruling", "value" : ""},{ "name" : "colour", "value" : "Blue"},{ "name" : "pages", "value" : ""},{ "name" : "packsize", "value" : ""},{ "name" : "personalisation", "value" : ""}]}"			Blue
Sheet1

Cell Formulas
Range	Formula
H2:M2	H2	=TRIM(TEXTBEFORE(TEXTAFTER(SUBSTITUTE($G2,CHAR(34),""),H$1&", value :"),"}"))

 

[Jaffabfc]

This in J2

=LET(sp,TEXTSPLIT(G2,": "),TEXTAFTER(FILTER(sp,ISNUMBER(SEARCH(", ",sp))),", "))

This doesnt let me enter the formula for some reason brings up an error straight away

 

[JEC]

I had semi colons as function separator. I did chagne the post, could you try again?

 

[Jaffabfc]

Try

Book1
	G	H	I	J	K	L	M
1		size	ruling	colour	pages	packsize	personalisation
2	"{"options": [{ "name" : "size", "value" : ""},{ "name" : "ruling", "value" : ""},{ "name" : "colour", "value" : "Blue"},{ "name" : "pages", "value" : ""},{ "name" : "packsize", "value" : ""},{ "name" : "personalisation", "value" : ""}]}"			Blue
Sheet1

Cell Formulas
Range	Formula
H2:M2	H2	=TRIM(TEXTBEFORE(TEXTAFTER(SUBSTITUTE($G2,CHAR(34),""),H$1&", value :"),"}"))

as much as this looks like it should work it doesnt seem to be working for me.
the string of data in cell G2 was sql does that make any difference?

 

[Cubist]

as much as this looks like it should work it doesnt seem to be working for me.
the string of data in cell G2 was sql does that make any difference?

What didn't work specifically?

 

[Jaffabfc]

I had semi colons as function separator. I did chagne the post, could you try again?

i have just tried it again and its bringing up #spill!

 

[JEC]

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

So clear j2,k2,l2 etc

 

[Jaffabfc]

What didn't work specifically?

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