That's sick!!

Okay, first of all - I'm serious. Without getting specific, this is a useful tool which is apparently omitted in the Mac version of Excel.

Now, with getting specific, here's what it does: It breaks a text string up, using whatever character you specify to do the breaking.

So, for instance, if I give the following command:

MyText = Split ("this-is-some-text" , "-")

Then MyText(1) equals "this," MyText(2)="is," and so on.

I used this today at work, when we were given lats and longs like:
36'53'02,
and needed to convert them into decimal form. In which case, the Split function came in awfully handy for separating the text into degrees, minutes, and seconds.

But, doesn't Excel actually have a function to do dms to dd conversions?

---

What it is, man, a low-down and funky feelin'

Wait a minute...I just did something like this the other day...I'm sure you can do it from a menu. I guess you can't use it as a function (like it seems you want to do), but I'm 99% certain (as I said, I did this last week, but can't find the menu button in a quick search) you can do it via a menu. You could probably make that into a macro if necessary.

-Tofer

Okay, I found what I was talking about. Data-->Text to Columns allows you to break a column into multiple columns by a delimiter such as "-" or ";" or whatever. Again, it's not a function (that I can tell), but it will work.

-Tofer

Dick Moore Wrote:
-------------------------------------------------------
> But, doesn't Excel actually have a function to do
> dms to dd conversions?

I see degrees-to-radians, but not the other thing.

Tofer Wrote:
-------------------------------------------------------
> Okay, I found what I was talking about.
> Data-->Text to Columns allows you to break a
> column into multiple columns by a delimiter such
> as "-" or ";" or whatever. Again, it's not a
> function (that I can tell), but it will work.

Right - I used that several times today. Unfortunately, it doesn't provide the sort of workflow efficiency that's provided with Split, wrapped up in a custom function.

> Right - I used that several times today.
> Unfortunately, it doesn't provide the sort of
> workflow efficiency that's provided with Split,
> wrapped up in a custom function.

I agree. Did you make it into a macro? That would have sped things up considerably, I would think.

-Tofer

Tofer Wrote:
-------------------------------------------------------
Better: a custom function. Two custom functions, really.

So, if cell AH contains 37'52'36.760 (and it does!), getting the decimal version results from
=DegreesDec(SplitMe(AH2,1,"'"),SplitMe(AH2,2,"'"),SplitMe(AH2,3,"'"))

Function DegreesDec(deg, min, sec)
If deg <= 0 Then
DegreesDec = -(Abs(deg) + (min / 60) + (sec / 3600))
Else
DegreesDec = (deg + (min / 60) + (sec / 3600))
End If
End Function

Function SplitMe(ByVal MyText As String, position As Integer, splitchar As String) As String
Dim TempText() As String
TempText = Split(MyText, splitchar)
SplitMe = TempText(position - 1)
End Function

Greg the dogsitter Wrote:
-------------------------------------------------------
> Better: a custom function. Two custom functions,
> really.

Good call, and nice function.

-Tofer