How to Convert Text to Degrees?

hunter204

New Member
Joined
Aug 22, 2011
Messages
12
Good Day,

I want to convert my text in cell A1 and it contains 9-16-32 and I would like to make a formula that the result would be in Cell A2 is (9º 16.32‘N) I wonder if it's possible. If it's possible I will be gladly appreciate your help. Thanks & God Bless
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

doublej_jj

Board Regular
Joined
Apr 9, 2010
Messages
147
I am doubtful that all of your entries are single digit, dash, double digit, dash, double digit. If by chance they are A2 would be
=LEFT(A1,1)&CHAR(186)&" "&MID(A1,3,2)&"."&RIGHT(A1,2)&"'N"

If the digits change, but a hyphen separates each segment, try:
=LEFT(A1,FIND("-",A1,1)-1)&CHAR(186)&" "&MID(A1,FIND("-",A1,1)+1,FIND("-",A1,FIND("-",A1,3))-FIND("-",A1,1)-1)&"."&RIGHT(A1,LEN(A1)-FIND("-",A1,3))&"'N"

Furthermore, I am assuming that not all are positive, but I'd have to see more samples of data to create a pattern. It isn't pretty, but it works!

Jeff
 

hunter204

New Member
Joined
Aug 22, 2011
Messages
12
I am doubtful that all of your entries are single digit, dash, double digit, dash, double digit. If by chance they are A2 would be
=LEFT(A1,1)&CHAR(186)&" "&MID(A1,3,2)&"."&RIGHT(A1,2)&"'N"

If the digits change, but a hyphen separates each segment, try:
=LEFT(A1,FIND("-",A1,1)-1)&CHAR(186)&" "&MID(A1,FIND("-",A1,1)+1,FIND("-",A1,FIND("-",A1,3))-FIND("-",A1,1)-1)&"."&RIGHT(A1,LEN(A1)-FIND("-",A1,3))&"'N"

Furthermore, I am assuming that not all are positive, but I'd have to see more samples of data to create a pattern. It isn't pretty, but it works!

Jeff
Yes they are all positive. In A1 "09-16-32" Result A2 is "9º 16.32‘N" as you can see the 09 will be "9º" and the 16 will be "16" while the 32 will become ".32" what I want to do is a formula that will copy the text format and convert them. I don't know if this is possible. Appreciated your help jeff.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
Hi
Welcome to the board

Try in B1:

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,"-",":",1),"-","."),"[m]º s.00")&"`N"

Copy down

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">09-16-32</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">9º 16.32`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">08-05-01</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">8º 5.01`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">75-23.89</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">75º 23.89`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>
 
Last edited:

hunter204

New Member
Joined
Aug 22, 2011
Messages
12
Hi
Welcome to the board

Try in B1:

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,"-",":",1),"-","."),"[m]º s.00")&"`N"

Copy down

<table style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" border="1" cellpadding="1"><tbody><tr><th style="border-width:1px;border-color:#888888;background:#9CF "> </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center">A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center">B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width="30">C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">09-16-32</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">9º 16.32`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">08-05-01</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">8º 5.01`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">75-23.89</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">75º 23.89`N</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan="4" style="background:#9CF; padding-left:1em"> [Book1]Sheet1</td></tr></tbody></table>
That's what i'm looking for. Thanks pgc01 for the help. appreciated it.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,199
Messages
5,473,114
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top