Convert value & symbols to just numbers

[Curtis Vaughan]

I need to figure out how to convert Lat. & Long. entries on an Excel spreadsheet to just the numeric value.
So for example we have 46° 43' N and 139º 56'E. I need this to be read as 46.43 and 139.56, respectively. How can I do that?

Thanks!

 

[Claus Busch]

Hi Curtis,

Am Tue, 22 Jan 2013 10:17:50 -0800 (PST) schrieb Curtis Vaughan:

I need to figure out how to convert Lat. & Long. entries on an Excel spreadsheet to just the numeric value.
So for example we have 46° 43' N and 139º 56'E. I need this to be read as 46.43 and 139.56, respectively. How can I do that?

your string in A1:
=LEFT(A1,FIND("°",A1)-1)+MID(A1,FIND("°",A1)+2,2)/100


Regards
Claus Busch

 

[isabelle]

hi Curtis,

=MID(A1,1,FIND(CHAR(186),A1)-1)+MID(A1,FIND(CHAR(186),A1)+1,3)/100

=MID(A2,1,FIND(CHAR(176),A2)-1)+MID(A2,FIND(CHAR(176),A2)+1,3)/100

isabelle


Le 2013-01-22 13:17, Curtis Vaughan a écrit :

 

[Curtis Vaughan]

Hi Curtis,



Am Tue, 22 Jan 2013 10:17:50 -0800 (PST) schrieb Curtis Vaughan:







your string in A1:

=LEFT(A1,FIND("�",A1)-1)+MID(A1,FIND("�",A1)+2,2)/100

Very close.
One it can't be a plus between the 2 as that just adds the 2 numbers together. It should be concatenate - &. Not the symbols "�" are not what I posted. I posted a degree symbol and a space between 40 and 42 (same for 139 and 56), but it's coming out as these symbols. Regardless, here's what happens. I tried the following formula, but I get 460.43

=LEFT(E41,FIND("°",E41)-1)&MID(E41,FIND("°",E41)+2,2)/100

 

[Curtis Vaughan]

Very close.

One it can't be a plus between the 2 as that just adds the 2 numbers together. It should be concatenate - &. Not the symbols "�" are not what I posted. I posted a degree symbol and a space between 40 and 42 (same for 139 and 56), but it's coming out as these symbols. Regardless, here's what happens. I tried the following formula, but I get 460.43



=LEFT(E41,FIND("°",E41)-1)&MID(E41,FIND("°",E41)+2,2)/100

Like this!
=(LEFT(E41,FIND("°",E41)-1)&MID(E41,FIND("° ",E41)+2,2))/100

 

[Ron Rosenfeld]

I need to figure out how to convert Lat. & Long. entries on an Excel spreadsheet to just the numeric value.
So for example we have 46° 43' N and 139º 56'E. I need this to be read as 46.43 and 139.56, respectively. How can I do that?

Thanks!

If your degree sign ° will always be the same, you could use:

=SUBSTITUTE(LEFT(SUBSTITUTE(A1,CHAR(176),"."),FIND("'",A1)-1)," ","")
to produce a text string or
=--SUBSTITUTE(LEFT(SUBSTITUTE(A1,CHAR(176),"."),FIND("'",A1)-1)," ","")
to produce a number.

If your degree sign is sometimes ° and sometimes º (they look the same here but the second, in many fonts, has an underscore under the °, as is shown in your longitude example, then:

=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(176),"."),CHAR(186),"."),FIND("'",A1)-1)," ","")
or
=--SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(176),"."),CHAR(186),"."),FIND("'",A1)-1)," ","")

 

[Alex Plantema]

Curtis Vaughan schreef in

One it can't be a plus between the 2 as that just adds the 2 numbers
together. It should be concatenate - &. Not the symbols "�" are not
what I posted. I posted a degree symbol and a space between 40 and 42
(same for 139 and 56), but it's coming out as these symbols.
Regardless, here's what happens. I tried the following formula, but I
get 460.43

=LEFT(E41,FIND("°",E41)-1)&MID(E41,FIND("°",E41)+2,2)/100

LEFT(A1,FIND("°",A1)-1) yields 46,
MID(A1,FIND("°",A1)+2,2) yields 43.
You should either divide the latter by 100 and add both results (Claus' solution),
or concatenate the strings, with a dot between them,
but not both divide and concatenate, as you did; that's why you got 460.43.