advertisement
Forums

The Forum is sponsored by 
 

AAPL stock: Click Here

You are currently viewing the Tips and Deals forum
OMG! VBA for Mac lacks the Split function!?
Posted by: Greg the dogsitter
Date: February 01, 2006 07:25PM
That's sick!!
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Greg the dogsitter
Date: February 01, 2006 07:35PM
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.
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Dick Moore
Date: February 01, 2006 08:39PM
But, doesn't Excel actually have a function to do dms to dd conversions?



What it is, man, a low-down and funky feelin'
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Tofer
Date: February 01, 2006 09:04PM
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
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Tofer
Date: February 01, 2006 09:08PM
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
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Greg the dogsitter
Date: February 01, 2006 09:56PM
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.
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Greg the dogsitter
Date: February 01, 2006 09:58PM
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.
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Tofer
Date: February 01, 2006 10:01PM
> 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
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Greg the dogsitter
Date: February 02, 2006 08:58AM
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
Options:  Reply • Quote
Re: OMG! VBA for Mac lacks the Split function!?
Posted by: Tofer
Date: February 02, 2006 07:39PM
Greg the dogsitter Wrote:
-------------------------------------------------------
> Better: a custom function. Two custom functions,
> really.

Good call, and nice function. smiling smiley

-Tofer
Options:  Reply • Quote
Sorry, only registered users may post in this forum.

Click here to login

Online Users

Guests: 93
Record Number of Users: 186 on February 20, 2020
Record Number of Guests: 5122 on October 03, 2020