0

i have values formatted in this format in a cell in excel:

{ "DATA1":"ABC" "DATA2":"DEF" "DATA3":123 "DATA4":456 }

could anyone please help me to figure out a formula to get for e.g. if i want to only extract the data in "DATA1" ?

many thanks!

xenos
  • 5
  • 3
  • Incidentally, if that's how your data is laid out then it's not [tag:JSON] (as tagged}. ([JSON](https://en.wikipedia.org/wiki/JSON) would have comma's.) – ashleedawg Nov 28 '21 at 09:41
  • Maybe you can work your way around this issue with [FILTERXML](https://stackoverflow.com/q/61837696/9758194). Split data on both colon and space, return data after 'DATA1' and do another substitute on quotes. – JvdV Nov 28 '21 at 10:05
  • If you only want the DATA1 result you can use a combination of MID/SEARCH. If you want it to be more dynamic you should use a solution with FILTERXML this requires a Windows environment. – P.b Nov 28 '21 at 10:33

1 Answers1

0

Try separating delimiters by Spaces：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,""" ",REPT(" ",99)),":""",REPT(" ",99)),99,99))
SeeRain
  • 76
  • 2