Hi,
Can you please help me pull out certain values from a text string?
I would like to use a formula and not code.
The string is very long and is a series of coordinates and is in cell A3 and is in the format below:
points:[{x:416.83624,y:379.13855,status:0},{x:415.50653,y:378.78394,status:2},{x:414.4428,y:379.13855,status:2},{x:413.02448,y:379.84772,status:2},{x:411.07425,y:380.7342,status:2} etc etc etc
I would like to create a 2 column table with the x & y coordinates in order they appear, i.e.
The formulas in each column will need to be dragged down potentially 10000 times to extract all coordinates from the cell.
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]416.8362[/TD]
[TD]379.13855[/TD]
[/TR]
[TR]
[TD]415.50653[/TD]
[TD]378.78394[/TD]
[/TR]
[TR]
[TD]414.4428[/TD]
[TD]379.13855[/TD]
[/TR]
[TR]
[TD]413.02448[/TD]
[TD]379.84772[/TD]
[/TR]
[TR]
[TD]411.07425[/TD]
[TD]380.7342[/TD]
[/TR]
</tbody>[/TABLE]
Note: the amount of decimals is not always the same. I am fine with it retrieving the first 3 decimals if that makes it easier (e.g. 416.836)
Thanks!
Can you please help me pull out certain values from a text string?
I would like to use a formula and not code.
The string is very long and is a series of coordinates and is in cell A3 and is in the format below:
points:[{x:416.83624,y:379.13855,status:0},{x:415.50653,y:378.78394,status:2},{x:414.4428,y:379.13855,status:2},{x:413.02448,y:379.84772,status:2},{x:411.07425,y:380.7342,status:2} etc etc etc
I would like to create a 2 column table with the x & y coordinates in order they appear, i.e.
The formulas in each column will need to be dragged down potentially 10000 times to extract all coordinates from the cell.
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]416.8362[/TD]
[TD]379.13855[/TD]
[/TR]
[TR]
[TD]415.50653[/TD]
[TD]378.78394[/TD]
[/TR]
[TR]
[TD]414.4428[/TD]
[TD]379.13855[/TD]
[/TR]
[TR]
[TD]413.02448[/TD]
[TD]379.84772[/TD]
[/TR]
[TR]
[TD]411.07425[/TD]
[TD]380.7342[/TD]
[/TR]
</tbody>[/TABLE]
Note: the amount of decimals is not always the same. I am fine with it retrieving the first 3 decimals if that makes it easier (e.g. 416.836)
Thanks!