Extracting Minutes From Coordinates (D-M-S)

[Ark68]

Consider these two georgraphic co-ordinates

Book3
	B	C	D	E	F	G	H	I
42	N56°51'50.2"	W101°04'31.2"			N	56	51	0.0
43	N50°00'47"	W110°47'57"			N	50	0	0.0
Table046 (Page 19)

Cell Formulas
Range	Formula
F42:F43	F42	=LEFT([@Column2],1)
G42:G43	G42	=VALUE(MID([@Column2],2,2))
H42:H43	H42	=VALUE(MID([@Column2],5,2))
I42:I43	I42	=IF(LEN(B42=10),0,VALUE(MID(B42,8,4)))

I am trying to extract the seconds values of these co-ordinates. Lets consider we're tring to extract the Longitudinal minutes from column B (in text) to column I (in decimal). You will notice that the data in column C is inconsistent. In my dataset, in column c, there are several values that do not have decimal second values as in C43 (47"), unlike the majority of data that does have a decimal seconds value like in C42 (50.2").

My formula in column I is failing. I am getting a value of all zeros, which is correct for that source data without decimal seconds (length of value in C = 10), however, the other values in C (whose length = 12) do not produce the desired result. In the illustrated case I am looking for a numeric value of 50.2. Column I is formatted as a number to one decimal place.

Can someone point out to me where the error in my formula is (column I)? Should I be going about this with a different approach perhaps?

 

[mumps]

Try:

=IF(LEN(B42)=10,0,MID(B42,8,4))

 

[Ark68]

That was a silly mistake. Tank you for not making me feel foolish.
I'm surprised it didn't throw an error.

 

[mumps]

You are very welcome.

 

[Cubist]

Here's an array option.

Book1
	B	C	D	E	F	G	H
42	N56°51'50.2"	W101°04'31.2"		N	56	51	50.2
43	N50°00'47"	W110°47'57"		N	50	00	47
Sheet4

Cell Formulas
Range	Formula
E42:H43	E42	=TEXTSPLIT(REPLACE(B42,2,0,"|"),{"|","°","'",""""},,1)
Dynamic array formulas.

 

[Peter_SSs]

Here's an array option.

.. but it doesn't produce the results that the OP seems to want in row 43 going by their attempted formula in post 1 and the marked solution in post 2.
(Though the apparent expected result doesn't make sense to me - and your results do. )

(Your formula also will not work in the OP's layout, which is a formal Excel table.)

 

[Cubist]

.. but it doesn't produce the results that the OP seems to want in row 43 going by their attempted formula in post 1 and the marked solution in post 2.
(Though the apparent expected result doesn't make sense to me - and your results do. )

(Your formula also will not work in the OP's layout, which is a formal Excel table.)

Can you explain why it wouldn't work? Do you mean the output range is a table? If so, then yes it won't work with spill range.

Book1
	B	C	D	E	F	G	H
41	Coord1	Coord2
42	N56°51'50.2"	W101°04'31.2"		N	56	51	50.2
43	N50°00'47"	W110°47'57"		N	50	00	47
Sheet4

Cell Formulas
Range	Formula
E42:H43	E42	=TEXTSPLIT(REPLACE(Table1[@Coord1],2,0,"|"),{"|","°","'",""""},,1)
Dynamic array formulas.

 

[Peter_SSs]

Do you mean the output range is a table?

Yes, look at the OP's first post. Data and formulas are clearly all in a table.