pull out multiple data from delimited columns

Cartesian

New Member
Joined
May 3, 2019
Messages
11
Hi all,

I'm back at it again and seeking help.
I have created a power query in excel that combines and edits multiple csv files in a folder.
The result is a table that looks like this, but has thousands of columns of data:

mrexcel.png

a131451gF05-5974

a131451gF05-5974


I would like to pull out the coordinates from the rows and dump them into columns on a different sheet in the workbook (potentially 2000 coordinate pairs).

For example, column A will pull out each "x:" coordinate , column B will pull out the corresponding "y:" coordinate from my query row 3.
Then column C will pull x: from query row 5, column D y: from query row 5
Then column E will pull x: from query row 7, column F y: from query row 7
and so on and so forth to produce something like this:

mrexcel2.png


Note: there are other random text values throughout the row, so will need to look for the x: and y: to retrieve the values.
Also note, the coordinates dont always have the same number of decimal places.


I will also need to pull out other information from rows 2, 4, 6, 8 etc.
so will retrieve "info1" without the id:, or 1.5 without the line:'' etc

Thanks so much!
 

Excel Facts

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

Forum statistics

Threads
1,215,472
Messages
6,125,005
Members
449,203
Latest member
Daymo66

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