Not sure what formula to use to sort

Occasional Visitor

I have spent a few hours trying to figure out what should be a simple task, and I was wondering if someone could assist me with which formula would be useful for what I am attempting to accomplish. 

I have pulled a SQL database that unfortunately contains a column which includes three separate data points in the single column.

It looks something like this: 

{"apples":4489,"oranges":1000,"bananas":0}

{"oranges":0,"bananas":0,"apples":1330}

{"bananas":2725,"oranges":101,"apples":100}

{"oranges":0,"apples":2386,"bananas":1578}

I have separated them into individual columns, but they're still not entirely easy to understand, I am looking to sort them by type into a specific column. 

 

Apples:4489

Oranges:1000

Bananas:0

Oranges:0

Bananas:0

Apples:1330

Bananas:2725

Oranges:101

Apples:100

Oranges:0

Apples:2386

Bananas:1578

 

How would I create a formula that sorts it into useable data such as this?

 

 

 

 

Apples

Bananas

Oranges

Apples:4489

Oranges:1000

Bananas:0

 

4489

0

1000

Oranges:0

Bananas:0

Apples:1330

 

1330

0

0

Bananas:2725

Oranges:101

Apples:100

 

100

2725

101

Oranges:0

Apples:2386

Bananas:1578

 

2386

1578

0

2 Replies

@MilesMiller 

Maybe with Power Query. I'd prefer a data layout like in the green table.

apples bananas oranges.JPG

 

@MilesMiller 

If you must have a formula and have access to 365, this formula might work. It will re-shape the data vertically. It's best to go vertical.

=LET(data,E1:G4,arr,SORT(TOCOL(data)),HSTACK(TEXTBEFORE(arr,":"),TEXTAFTER(arr,":")))