Option Explicit
Function mySplit(strToSplt, strSplitOn)
Dim ip1, ip2
Dim strArray()
Dim iCount
ip1 = 1: ip2 = 1
Do
ip2 = InStr(ip1, strToSplt, strSplitOn)
If ip2 = 0 Then
ip2 = Len(strToSplt) + 1
End If
If iCount Mod 100 = 0 Then
ReDim Preserve strArray(iCount + 100)
End If
strArray(iCount) = Mid$(strToSplt, ip1, ip2 - ip1)
ip1 = ip2 + Len(strSplitOn)
iCount = iCount + 1
Loop Until ip2 >= Len(strToSplt)
ReDim Preserve strArray(iCount - 1)
mySplit = strArray
End Function
Sub test()
Dim intRow, intThisOne As Integer
Dim rw As Variant
Dim MyStr, strParams, strUserName, strSubContext, strContext, strTree As String
intRow = 1
'Do this for every row in worksheet
For Each rw In Worksheets("allusers").Cells(1, 1).CurrentRegion.Rows
MyStr = rw.Cells(1, 1).Value 'Get the string to split up
strParams = mySplit(MyStr, ".") 'Send string tho be split
If UBound(strParams) = 2 Then intThisOne = 1 'username.context.tree
If UBound(strParams) = 1 Then intThisOne = 2 'username.tree
If UBound(strParams) = 3 Then intThisOne = 3 'username.subcontext.context.tree
Select Case intThisOne
Case 1 'username.context.tree
strUserName = Trim(strParams(0))
strSubContext = ""
strContext = Trim(strParams(1))
strTree = Trim(strParams(2))
Case 2 'username.tree
strUserName = Trim(strParams(0))
strSubContext = ""
strContext = ""
strTree = Trim(strParams(1))
Case 3 'username.subcontext.context.tree
strUserName = Trim(strParams(0))
strSubContext = Trim(strParams(1))
strContext = Trim(strParams(2))
strTree = Trim(strParams(3))
End Select
If strUserName <> "" Then
rw.Range("C" & intRow).Value = strUserName 'put the username in column D and the row
rw.Range("D" & intRow).Value = strSubContext 'put the subcontext in column D and the row
rw.Range("E" & intRow).Value = strContext 'put the context in column D and the row
rw.Range("F" & intRow).Value = strTree 'put the tree in column E and the row
End If
Next rw
End Sub