Macro attempting to split cells containing multiple co-ordinates into appropriate columns

Hi Everyone,

I am currently attempting to split cells that contain an undetermined amount of Co-Ordinates (in the format of 'LINESTRING(X Y Z,X Y Z,X Y Z)' etc) into appropriate columns in a spreadsheet.

Due to the fact that the cell can potentially contain between 2 and an infinite number of X Y Z co ordinates i am looking to extract only the first and last sets and delete the Z co-ordinate from each set. The resulting outcome would ideally be:

First X Co-ordinate | First Y Co-ordinate | Second X Co-ordinate | Second Y Co-ordinate
X| Y| X| Y

Due to the fact that each co-ordinate is separated by a space, as far as i can gather the best way to do this would be to select the individual co ordinates by extracting them based on the position of spaces in the cell?

(In addition the Z co-ordinate does not always populate so it is not possible to achieve this by counting a number of characters to the left or right to determine the correct point in which to split the cell)

I would appreciate any and all assistance as i'm a little stumped at the moment.

Thank you all in advance

Hi,

see Split and UBound functions in VBA inner help …

Hi Bulina2k,

Thank you for your solution. It seems to work perfectly for examples where there are 2 sets of co ordinates, however when there are more than 2 it appears to return the second set and not the final set.

Is there a slight alteration that i should make to amend this?

See if this works

Please Login or Register  to view this content.

Brilliant.

Thanks both of you. Made a couple of minor tweaks to your solution Stephen and it works perfectly.

Much appreciated for both of your time.