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

Question

My VBA Excel code is using the split function 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 it does not work when there are only 5 hyphens, 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 I can't get it right. I also tried some strreverse but got a 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 "-".

• Ike: What is your requirement? To retrieve the last entry of the list?
• Dasal Kalubowila: everything right of final "-". there are either 5 or 6 dashes in current data.

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)

• Dasal Kalubowila: Many thanks. This solution works nice. Appreciate the explantion too.

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.

• Dasal Kalubowila: thanks Storax. This worked as well. Similar soultion to that provided by FunThomas. I went for that as a slightly shorter function.
• Storax: To keep things clear, I used a verbose version. But if needed, my function can be shortened, just replace the if ... then ... else ... end if part with getLastPart = IIf(pos > 0, Trim(Mid(s, pos + 1)), s). FunThomas could likely do the same with his function.

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.

• FunThomas: The lower bound of the result of Split is always 0, even if you use Option Base 1.
• Atmo: Yes, that is exactly what I said. I was specifically talking about "other ways to create arrays", and the last snippet of the code I posted uses an hypothetical CustomArray() function for that reason.
• FunThomas: Misunderstood what you mean with "But if you are not guaranteed the first index of Arr is 0"
• Atmo: I'll think of a way to rephrase it then, thx for the comment.