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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
for the first part:

xystatus
414.4428​
379.13855​
2​
415.50653​
378.78394​
2​
416.83624​
379.13855​
0​

is that what you want?
 

Cartesian

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
no problem, I can replace status with defined azimuth,

like this?

xyazimuth
414.4428​
379.13855​
23​
415.50653​
378.78394​
23​
416.83624​
379.13855​
23​

edit:
post a link to the shared csv file
 
Last edited:

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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:

Cartesian

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

Forum statistics

Threads
1,141,068
Messages
5,704,095
Members
421,327
Latest member
Msh

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
Top