Convert Declination into Decimal

[will20fitz]

hi there, i'm trying to find an efficient way to convert astronomical declination into decimal format.
The format is as below. I have tried text to columns using :, but i want something that i can do in one column... i don't want to create three and perform calcs on these and then sum in a fourth column.

Can someone suggest something?

I Did find the below VBA code but i dont know how to update to pickup the format i have the declinations in!

Function Convert_Decimal(Degree_Deg As String) As Double
' Declare the variables to be double precision floating-point

Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
' Set degree to value before "°" of Argument Passed.
Degree_Deg = Replace(Degree_Deg, "~", "°")


degrees = CDbl(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
' Set minutes to the value between the "°" and the "'"
' of the text string for the variable Degree_Deg divided by
' 60. The Val function converts the text string to a number.
minutes = CDbl(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 1, _
InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, "°") - 1)) / 60
' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = CDbl(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
1, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 1)) / 3600

Convert_Decimal = degrees + minutes + seconds

End Function

Sun_a_dec

-22:54:16.1

-22:48:30.8

-22:42:18.3

-22:35:38.9

-22:28:32.6

-22:20:59.9

-22:13:00.8

-22:04:35.6

-21:55:44.6

-21:46:28.0

-21:36:46.1

-21:26:39.1

-21:16:07.4

-21:05:11.2

-20:53:50.8

-20:42:06.5

-20:29:58.6

-20:17:27.5

-20:04:33.5

-19:51:16.8

-19:37:37.9

-19:23:37.0

-19:09:14.6

-18:54:31.1

 

[Anthony47]

If you have Office 365 or 2021, try in B2: =LET(myDeg,A2,Split,TEXTSPLIT(myDeg,":"),Mux,IF(LEFT(myDeg,1)="-",-1,1), INDEX(Split,1)+(INDEX(Split,2)+INDEX(Split,3)/60)/60*Mux)

Or try this UDF:

Function DegToDec(ByRef myDeg As String) As Double
Dim mySplit, MuX As Long
'
mySplit = Split(myDeg, ":", , vbTextCompare)
If Left(mySplit(0), 1) = "-" Then MuX = -1 Else MuX = 1
DegToDec = mySplit(0) + (mySplit(1) + mySplit(2) / 60) / 60 * MuX
End Function

Then in A2: =DegToDec(A2)

 

[will20fitz]

thanks SO much, this is perfect. All the best, Will