Coordinates from text & rotate

Cartesian

New Member
Joined
May 3, 2019
Messages
11
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.

dump.png


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

Thanks & godspeed!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
for the first part:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]x[/td][td=bgcolor:#70AD47]y[/td][td=bgcolor:#70AD47]status[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
414.4428​
[/td][td=bgcolor:#E2EFDA]
379.13855​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
415.50653​
[/td][td]
378.78394​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
416.83624​
[/td][td=bgcolor:#E2EFDA]
379.13855​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]
[/table]


is that what you want?
 
Upvote 0
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 ?
 
Upvote 0
no problem, I can replace status with defined azimuth,

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]x[/td][td=bgcolor:#70AD47]y[/td][td=bgcolor:#70AD47]azimuth[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
414.4428​
[/td][td=bgcolor:#E2EFDA]
379.13855​
[/td][td=bgcolor:#E2EFDA]
23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
415.50653​
[/td][td]
378.78394​
[/td][td]
23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
416.83624​
[/td][td=bgcolor:#E2EFDA]
379.13855​
[/td][td=bgcolor:#E2EFDA]
23​
[/td][/tr]
[/table]


edit:
post a link to the shared csv file
 
Last edited:
Upvote 0
the first part of your question :
Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top