Possible to switch numbers around?

[James432213]

Hi,

I have a column with entries like so,

POINT (174.7753931 -36.8867484)
POINT (174.7749403 -36.89297140000001)
POINT (174.7604037 -36.8692689)



Is it possible to switch it so it reads?

POINT (-36.8867484 174.7753931)
POINT (-36.89297140000001 174.7749403)
POINT (-36.8692689 174.7604037)

Thank you

[FDibbins]

Are there always only ever 2 numbers?
Do they always have a space between them?
Is that the only space in that string?

[FDibbins]

If your data starts in L4...
="POINT ("&LEFT(MID(L4,FIND("(",L4)+1,99),FIND(" ",MID(L4,FIND("(",L4)+1,99))-1)&" -"&LEFT(MID(L4,FIND("-",L4)+1,99),LEN(MID(L4,FIND("-",L4)+1,99))-2)&")"

Hopefully some1 will be able to simplify that for you (wife is calling me for supper)

[James432213]

Are there always only ever 2 numbers?

Yes

Do they always have a space between them?

Yes

Is that the only space in that string?

There are two spaces, one between "POINT (" and one between the two numbers

[James432213]

If your data starts in L4...
="POINT ("&LEFT(MID(L4,FIND("(",L4)+1,99),FIND(" ",MID(L4,FIND("(",L4)+1,99))-1)&" -"&LEFT(MID(L4,FIND("-",L4)+1,99),LEN(MID(L4,FIND("-",L4)+1,99))-2)&")"

Hopefully some1 will be able to simplify that for you (wife is calling me for supper)

Thank you

but I'm a complete noob with Excel, how do I use this? Also, by L4 do you mean column letter L and row 4?

[Rick Rothstein]

Since, as a localized "earth" coordinate, your second number will always start with a minus sign, and assuming your data is in Column A starting on Row 1, here is another formula that you can consider...

="POINT ("&SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("-",A1),99),")"," ")&LEFT(A1,FIND("-",A1)-2),"POINT (","")&")"

[James432213]

Since, as a localized "earth" coordinate, your second number will always start with a minus sign, and assuming your data is in Column A starting on Row 1, here is another formula that you can consider...

="POINT ("&SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("-",A1),99),")"," ")&LEFT(A1,FIND("-",A1)-2),"POINT (","")&")"

Thank you so much!

How would I do the opposite?

So,

POINT (-36.8867484 174.7753931)
POINT (-36.89297140000001 174.7749403)
POINT (-36.8692689 174.7604037)

becomes

POINT (174.7753931 -36.8867484)
POINT (174.7749403 -36.89297140000001)
POINT (174.7604037 -36.8692689)

[Rick Rothstein]

How would I do the opposite?

Give this formula a try...

=SUBSTITUTE(LEFT(A1,FIND(" ",A1,FIND("(",A1))-1)&")","(","("&SUBSTITUTE(MID(A1,FIND(" ",A1,FIND("(",A1))+1,99),")"," "))

[FDibbins]

Nicely done Rick, I was having a brain phart.