# How to Convert Text to Degrees?

#### hunter204

##### New Member
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

### 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
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
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
Hi
Welcome to the board

Try in B1:

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

Copy down

Last edited:

#### hunter204

##### New Member
Hi
Welcome to the board

Try in B1:

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

Copy down

That's what i'm looking for. Thanks pgc01 for the help. appreciated it.

#### Armando Montes

##### Well-known Member
Custom formula the cells: 00"°"00"'"00.00\"
=SUBSTITUTE(A1,"-","")+0