Organize a large cell of data (tough task)

[TestMailinator]

Hi all,

I'm trying to figure out how to pull certain data from a large set of raw data that is pulled automatically(API).

When the data is pulled in, a single cell shows: { "total": "343", "limit": "2", "offset": "0", "data": { "0": { "DATA1": "info24", "DATA2": { "0": "tag" }, "Cp": "1.30", "DATA3": "170", "DATA4": { "20140317": "1", "20140318": "1" } }, "1": { "INFO1": "info34", "INFO2": { "0": "tag" }, "Cp": "0.00", "INFO3": "170", "INFO4": { "20140317": "1", "20140318": "1" } } } }

I want to create a formula that will output and organize the data like:

Column 1 Column 2
DATA1 info24
DATA2 info34

I've been stuck on this for the whole day. I can't change or delimit the original raw data since its dynamic and sometimes there may need to be more rows of data, in this case it is only two.

Anyone got any creative solutions?

Thanks in advance!

[soberguy]

If that string is in, say, A1, you could do something like this in in your new cell where you want the data:

=MID(A1,FIND("DATA1",A1)+9,6)

Note that it finds DATA1 without the quotes. +9 is to extend the start position for the MID function. 6 is the length of text to return.

Should "info24" be a variable length string, then you can use the same approach to find the comma after "DATA1" - FIND(",",A1,FIND("DATA1",A1)). Then you can use the LEN function to get exact positions for the MID to work.

Hope that makes sense, or at least gets you going in the right direction.

[kvsrinivasamurthy]

Pl see file
ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

Please Login or Register  to view this content.