I have a function that I am using below. I want to be able to pass all the
operators to the variable Operators (i.e.Operators = "+" & "-" & "/" & "*" &
"^"). However when I do this, the function fails. It only works when I pass
one character to the variale. How I do pass more than one character to this
variable and then have it work proplerly in the Split function? Thanx

Function functionseparator(str As String) As String
Dim Operators As String
Dim temp

Operators = "+"

temp = Split(str, Operators)
functionseparator = temp(UBound(temp) - 1)

End Function

I may be mis-understanding, but you seem to want to have multiple separators
for split.

Does this do what you want?

Function functionseparator(str As String) As String
Dim Operators
Dim temp
Dim i As Long
Dim iPos As Long
Dim iStart As Long

Operators = Array("+", "/", "*","^")

iStart = 1
ReDim temp(1 To 1)
For iPos = 1 To Len(str)
For i = LBound(Operators) To UBound(Operators)
If Mid(str, iPos, 1) = Operators(i) Then
itemp = itemp + 1
ReDim Preserve temp(1 To itemp)
temp(itemp) = Mid(str, iStart, iPos - iStart)
iStart = iPos + 1
Exit For
End If
Next i
Next iPos
ReDim Preserve temp(1 To itemp + 1)
temp(itemp + 1) = Mid(str, iStart, iPos - iStart)
functionseparator = temp(UBound(temp) - 1)

End Function


--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
I have a function that I am using below. I want to be able to pass all
the
operators to the variable Operators (i.e.Operators = "+" & "-" & "/" & "*"
&
"^"). However when I do this, the function fails. It only works when I
pass
one character to the variale. How I do pass more than one character to
this
variable and then have it work proplerly in the Split function? Thanx

Function functionseparator(str As String) As String
Dim Operators As String
Dim temp

Operators = "+"

temp = Split(str, Operators)
functionseparator = temp(UBound(temp) - 1)

End Function

You need regular expressions not split. see regexp library:

http://msdn.microsoft.com/library/de...sobjRegExp.asp

Dm Unseen

What do you want to do?

What do you expect to be returned for the data like


1+ 2 / 3 * 5 ^ 12

??


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=388273

As always Bob, you the man!

Thanks

"Bob Phillips" wrote:

I may be mis-understanding, but you seem to want to have multiple separators
for split.

Does this do what you want?

Function functionseparator(str As String) As String
Dim Operators
Dim temp
Dim i As Long
Dim iPos As Long
Dim iStart As Long

Operators = Array("+", "/", "*","^")

iStart = 1
ReDim temp(1 To 1)
For iPos = 1 To Len(str)
For i = LBound(Operators) To UBound(Operators)
If Mid(str, iPos, 1) = Operators(i) Then
itemp = itemp + 1
ReDim Preserve temp(1 To itemp)
temp(itemp) = Mid(str, iStart, iPos - iStart)
iStart = iPos + 1
Exit For
End If
Next i
Next iPos
ReDim Preserve temp(1 To itemp + 1)
temp(itemp + 1) = Mid(str, iStart, iPos - iStart)
functionseparator = temp(UBound(temp) - 1)

End Function


--
HTH

Bob Phillips

"ExcelMonkey" wrote in message
...
I have a function that I am using below. I want to be able to pass all
the
operators to the variable Operators (i.e.Operators = "+" & "-" & "/" & "*"
&
"^"). However when I do this, the function fails. It only works when I
pass
one character to the variale. How I do pass more than one character to
this
variable and then have it work proplerly in the Split function? Thanx

Function functionseparator(str As String) As String
Dim Operators As String
Dim temp

Operators = "+"

temp = Split(str, Operators)
functionseparator = temp(UBound(temp) - 1)

End Function

In that case, you don't need Split function

Code
-------------------

Function functionseparator(txt As String)
Dim delim, x As Integer, num(4) As Integer, i, ii, Maxnum
delim = Array("+", "/", "*", "^", "-")
For i = LBound(delim) To UBound(delim)
x = InStrRev(txt, delim(i))
num(ii) = x: ii = ii + 1
Next
Maxnum = Application.Max(num)
functionseparator = Right(txt, Len(txt) - Maxnum)
End Functio
-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38827