0

I have problems using Visual Basic in Ms. Excel. I have a sort of coordinates data in DMS format (longitude and latitude) but it's not in a usual format. The data seems like this:

 E 103 29 12.4562 
 W 3 9 1.4562 
 N 16 5 32.4333 
 S 16 5 2.4333

I want to convert it to a standard decimal format. However, I've never used Visual Basic before. The convention for E, N and W, S are 1 and -1, respectively. I am expecting the output from that data list are 

 103.4867934
 -3.1504045
 16.09234258
 -16.08400925

I hope someone can make a visual basic code to convert this data because I have lots of data like this and I can't convert it manually.

Thanks.

Community
  • 1
  • 1
Thomas Panji
  • 1
  • 1

1 Answers1

0

instead of VBA you can use this formula:

=--(INDEX({"+","+","-","-"},MATCH(LEFT(A1,1),{"E","N","W","S"},0))&MID(A1,3,FIND(" ",MID(A1,3,LEN(A1))))+MID(A1,FIND("}}}",SUBSTITUTE(A1," ","}}}",2))+1,2)/60+MID(A1,FIND("}}}",SUBSTITUTE(A1," ","}}}",3))+1,99)/3600)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Well, that's a long formula I have to understand with, but thank you. I learn it as fast as I can. I am still open for receiving any answer regarding this question especially on the VBA code anyway. – Thomas Panji Aug 14 '17 at 17:13