Convert and extract lat longs into separate fields

[loto]

Hi there,
I've extracted some lats and longs from Google and want to split them into separate cells and remove the degrees character and direction. Any idea how to do this in one go? e.g. I'd like to convert a whole load of lat/longs in one text string in a cell from this format to this:

e.g lat long cardiff

From:

51.4780° N, 3.1771° W

To:

51.4780,-3.1771

e.g.

lat long cape town

From:

33.9767° S, 18.4244° E

To:

-33.9767, 18.444

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

Any help as always would be much appreciated,
Thanks
Loto

 

[Gary's Student]

With data in A1, in B1 enter:
=LEFT(A1,FIND(",",A1)-4)*IF(LEN(SUBSTITUTE(A1,"S",""))=LEN(A1),1,-1)
and in C1 enter:
=MID(A1,FIND(", ",A1)+2,FIND(", ",A1)-5)*IF(LEN(SUBSTITUTE(A1,"W",""))=LEN(A1),1,-1)

Thus if A1 contains:

51.4780° N, 3.1771° W B1 will display 51.478 and C1 will display -3.1771 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[loto]

Thanks so much - you are awesome it works beautifully