Excel – Reached the limit of my knowledge

Associate
Joined
9 May 2005
Posts
859
Location
Devon
I have a basic understanding of the LEFT, RIGHT & MID function but I am struggling to get to get this formula to work.

Basically I am trying to extract part of a cell and convert it to decimal degrees at the same time.

A1 10°12´N 1884
A2 6°58´S 2000

So from the above examples I want the answers

B1 10.20
B2 -6.97

=IF(RIGHT(A8,1)="S",ROUND(LEFT(A8,2)+MID(A8,4,2)/60, 3)*-1, ROUND(LEFT(A8,2)+MID(A8,4,2)/60, 3))

Works a treat unless there's 1 or three digits in front of the ° symbol

=LEFT(A8,FIND("°",A8&"°")-1)

Finds the characters in front of the ° symbol but I can’t work out how to integrate it.

=IF(RIGHT(A8,1)="S",ROUND(LEFT(A8, FIND("°",A8&"°")-1))+MID(A8,4,2)/60, 3)*-1, ROUND(LEFT(A8, FIND("°",A8&"°")-1))+MID(A8,4,2)/60, 3))

Doesn’t work.

I don’t know if this make sense to anyone but my head is spinning and a pointer in the right direction would be appreciated.

Thanks

Hallu
 
You're basically there you just needed to include the FIND function within your MID function so that you're using the correct starting location for everything after °.

Code:
=IF(RIGHT(A1,1)="S",ROUND(LEFT(A1, FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,2)/60, 3)*-1, ROUND(LEFT(A1, FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,2)/60, 3))

This still assumes that everything after ° is 2 digits. If this isn't always the case then you would need to use another FIND in the MIDs num_chars. You could probably use the ' before N or S as the reference point if that is there for every entry.
 
You're basically there you just needed to include the FIND function within your MID function so that you're using the correct starting location for everything after °.

Code:
=IF(RIGHT(A1,1)="S",ROUND(LEFT(A1, FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,2)/60, 3)*-1, ROUND(LEFT(A1, FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,2)/60, 3))

This still assumes that everything after ° is 2 digits. If this isn't always the case then you would need to use another FIND in the MIDs num_chars. You could probably use the ' before N or S as the reference point if that is there for every entry.

That's it.

Thanks,

Slack
 
Back
Top Bottom