Excel – Reached the limit of my knowledge

Slackjaw190 · 12 Aug 2014 at 22:01

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

bremen1874 · 13 Aug 2014 at 00:06

That looks like a job for a VBA script.

There's a example here that shouldn't be too difficult to modify to meet your needs...

https://www.youtube.com/watch?v=0QxxuSIFdFU

Slackjaw190 · 13 Aug 2014 at 00:26

I was trying to avoid VBA

andshrew · 13 Aug 2014 at 12:31

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.

Slackjaw190 · 13 Aug 2014 at 19:22

andshrew said:
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