Gsheet - Split Values of Cell

Question

I'm using

=importjson to import this API ("https://bitmax.io/api/pro/v1/depth?symbol=RUNE/USDT")

in a cell of Gsheet documents. The result is this:

Result

Is there a way to split the value in a cell in this way?

This way to format the result of API call

MattKing · Accepted Answer · 2020-02-06T14:17:36.593

0

try this:

=ARRAYFORMULA(VLOOKUP("X";{"X"\SPLIT(SUBSTITUTE(SUBSTITUTE(G7;",";"|");".";",");"|")};SEQUENCE(COLUMNS(SPLIT(SUBSTITUTE(SUBSTITUTE(G7;",";"|");".";",");"|"))/2;2;2);0))

If that doesn't work, it'd help if you shared a sample sheet.

edited Feb 06 '20 at 14:17

answered Feb 05 '20 at 15:15

MattKing

7,373
8
13

https://ibb.co/0qJ01QQ Seems that there is problem with point ( " . " ) because first column output is wrong while second column output is okay – Pierandrea Feb 05 '20 at 16:11
https://docs.google.com/spreadsheets/d/1Nnh4fgVHLlQx1UBN9XhEAm_atI3N7v92tSkVFDerA1s/edit?usp=sharing This is a sample sheet – Pierandrea Feb 05 '20 at 16:35
Can you make the sheet editable? – MattKing Feb 05 '20 at 19:57
Done, sorry! Thank you – Pierandrea Feb 06 '20 at 12:19
I used two SUBSTITUTE() formulas to swap the decimals "." (UK) for the decimals "," (Euro) before doing the split. That seems to have worked. I've updated my formula in my answer. Take a look and see if that's better? – MattKing Feb 06 '20 at 14:16
Awesome Matt!Super thank you! <3 – Pierandrea Feb 10 '20 at 13:28

Gsheet - Split Values of Cell

1 Answers1