# Coordinates from text & rotate

#### Cartesian

##### New Member
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!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### sandy666

##### Banned - Rules violations
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

##### New Member
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
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

Last edited:

#### Repush

##### Board Regular
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
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!

Replies
10
Views
2K
Replies
0
Views
655

1,191,536
Messages
5,987,162
Members
440,082
Latest member
belodelokelo

### 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.

### Which adblocker are you using?

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

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