Coordinates from text & rotate

[Cartesian]

Hi,
I have a series of .csv files that contain 2d x&y coordinates like so

{x:416.83624,y:379.13855,status:0},{x:415.50653,y:378.78394,status:2},{x:414.4428,y:379.13855,status:2},...

There are thousands of these points, all in text in one cell.
How can I pull out the x & y coordinates and drop them into a table?
For example

COLUMN A
416.83624
415.50653
414.4428
...

COLUMN B
379.13855
378.78394
379.13855
...

And the second part of the question - If I provide an origin, i.e. x0=5000, y0=10000 and z0=1000 and an azimuth =23
Is it possible to create a second table where these original 2d points are added to the 3d origin and rotated at the given azimuth?
Eg, my originally flat coordinates will the brought upright (original y coords become z) and the plane rotated 23 degrees about the origin.

NOTE: The x0, y0, z0 and azimuth values will change each .csv

Thanks & godspeed!

 

[sandy666]

for the first part:

x	y	status
414.4428​	379.13855​	2​
415.50653​	378.78394​	2​
416.83624​	379.13855​	0​

is that what you want?

 

[Cartesian]

Yes, basically. Just the first 2 columns X & Y. But I need them to read the text string and pull them out...there are thousands of them so the table will be quite long.
The status part is not important.

Then perhaps the New 3d co-ordinates in adjacent columns ?

 

[sandy666]

no problem, I can replace status with defined azimuth,

like this?

x	y	azimuth
414.4428​	379.13855​	23​
415.50653​	378.78394​	23​
416.83624​	379.13855​	23​

edit:
post a link to the shared csv file

 

[Repush]

the first part of your question :

Sub GetCoords()
    Dim hf As Integer: hf = FreeFile
    Dim lines() As String, Filename As String
    Dim i As Long
    Dim coords()
    
    Filename = Application.GetOpenFilename(FileFilter:="csv-file (*.csv),*.csv", Title:="Select file")

    Open Filename For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hf"]#hf[/URL] 
        lines = Split(Input$(LOF(hf), [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hf"]#hf[/URL] ), "},{")
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hf"]#hf[/URL] 
    coords = Array()
    ReDim coords(UBound(lines))
    For i = 0 To UBound(lines)
        lines(i) = Replace(lines(i), "{", "")
        lines(i) = Replace(lines(i), "}", "")
        lines(i) = Replace(lines(i), "x:", "")
        lines(i) = Replace(lines(i), "y:", "")
        lines(i) = Replace(lines(i), "status:", "")
        coords(i) = Split(lines(i), ",")
    Next i
    ActiveCell.Resize(UBound(lines) + 1, 3) = Application.Transpose(Application.Transpose(coords))
End Sub

 

[Cartesian]

Thanks Repush.

Im thinking will combine multiple .csv files into one and dump into excel.
So, can these X & Y coordinates be pulled out of the text string by formula and not code?

Thanks!