Split function with if statement to define string value (excel vba)

Question

My vba excel code is using split funtion from a combo box value. It takes an ID (alias=GG) which is a value right of the 6th hyphen.

GG = Split(Sheet1.ComboBox1.Value, "-")(6)

example return good :) (6hyphens) =GG = 414

Butt Road - 2 Tenures - 06 - Practical Completion - 33 Units - Dave Hampton - 414

The code works but not foolproof as does not work when there are only 5 hypens, returning blank.

example return fail :( (5 hypens only) GG=""

'Blaker Court - 1 Tenures - Completed (Outturn) - 14 Units - Sonya Barton - 412'

I can't change any standing data.

I would like a better way in my excel VBA for an if statement to define the string, but can't get it right. I also tried some strreverse but got method object fail.

in SQL the case statement  would be .
case when Split(Sheet1.ComboBox1.Value, "-")(6)` = 0 then Split(Sheet1.ComboBox1.Value, "-")(5)` else Split(Sheet1.ComboBox1.Value, "-")(6)` end as GG.  

can you help translate this into a vba if statement to define GG?

or even better, to take everything right of final "-".

Answer 1

Save the result of Split in a variable (it's an array of string) and check the last element.

In VBA, the first and the last element of an array can be checked using the functions LBound and UBound (lower bound and upper bound). For the result of Split, LBound is always 0 and UBound gives you the last element. If it returns 5, you have 6 elements (index 0..5)

If you pass an empty string into Split, Ubound will be -1, signaling that there are no elements.

To get the last element, you could write a small function:

Function GetLastElement(s As String, Optional delimiter As String = "-") As String
    if s = "" Then Exit Function
    Dim a() As String
    a = Split(s, delimiter)
    GetLastElement = a(UBound(a))
End Function

and use it like this:

GG = GetLastElement(Sheet1.ComboBox1.Value)

Answer 2

Using Split is not necessarily required to extract the last part of a string in VBA

Sub noSplit()
    Dim s As String
    s = "Butt Road-2 Tenures-06-Practical Completion-33 Units-Dave Hampton - 414"

    MsgBox "Result " & getLastPart(s)
End Sub

The function getLastPartlooks like

Function getLastPart(s As String, Optional delimiter As String = "-") As String
    Dim pos As Long
    
    ' Position of last hyphen
    pos = InStrRev(s, delimiter) ' extract text after last delimiter
    If pos > 0 Then
        getLastPart = Trim(Mid(s, pos + 1))
    Else
        getLastPart = s  ' no delimiter, return complete string
    End If

End Function

and you could call it in your code like GG = getLastPart(Sheet1.ComboBox1.Value)

PS: Based on the comment, here's a slightly shorter version—though it does reduce readability somewhat.

Function getLastPart(s As String, Optional delimiter As String = "-") As String
    Dim pos As Long
    pos = InStrRev(s, delimiter)
    getLastPart = IIf(pos > 0, Trim(Mid(s, pos + 1)), s)
End Function

When to use IIf

• For simple, safe expressions where both branches are guaranteed to be valid and lightweight.

• When you want a compact one-liner for assigning values based on a condition.

Better alternative: If...Then...Else

For anything beyond trivial logic, use the standard If...Then...Else structure. It’s safer, clearer, and only evaluates the necessary branch.

Answer 3

You can check for UBound which gives you the size of your array:

Try this example of who to proceed

Sub test()

Dim t As String, arrT As Variant
t = "Butt Road - 2 Tenures - 06 - Practical Completion - 33 Units - Dave Hampton - 414"
't= "Blaker Court - 1 Tenures - Completed (Outturn) - 14 Units - Sonya Barton - 412"

arrT = Split(t, "-")

Debug.Print arrT(UBound(arrT))

End Sub

Answer 4

The important thing to understand is that Split(Sheet1.ComboBox1.Value, "-") returns an array.

In VBA, it is very easy to access the last item of an array thanks to UBound (meaning upper bound):

Arr = Split(Sheet1.ComboBox1.Value, "-")
GG = Arr(UBound(Arr))

---

PS: 1 little thing to be careful about, the documentation for Split says:

Returns a zero-based, one-dimensional array containing a specified number of substrings.

However, VBA has other ways to create arrays, not all of which start at index 0.

Let us imagine you could have anywhere between 1 and 10 elements in the array, and you want to return:

• For any array with 7 or more elements, the 7th element.
• For any array shorter than 7 elements, the last element.

You could do:

Arr = Split(Sheet1.ComboBox1.Value, "-")
Arr(VBA.IIf(UBound(Arr) >= 6, 6, UBound(Arr)))

But if you are not guaranteed the first index of Arr is 0 (i.e. since Split returns an array starting at 0, as per its documentation, I am talking about a general case where the array was created by another function, e.g. a user-defined CustomArray()), accessing the 7th element is done using the LBound (meaning lower bound function) in that fashion:

Arr = CustomArray()
Arr(VBA.IIf(UBound(Arr) >= LBound(Arr) + 6, LBound(Arr) + 6, UBound(Arr)))

For more information, I recommend this reading about the Option Base statement.