Test if string begins with a string?

Question

In VBA, what's the most straight forward way to test if a string begins with a substring? Java has startsWith. Is there a VBA equivalent?

Do you know the length of the substring you want to find? – Blackhawk Dec 27 '13 at 17:59 — Blackhawk, Dec 27 '13 at 17:59

score 172 · Accepted Answer · answered Dec 27 '13 at 15:04

There are several ways to do this:

InStr

You can use the InStr build-in function to test if a String contains a substring. InStr will either return the index of the first match, or 0. So you can test if a String begins with a substring by doing the following:

If InStr(1, "Hello World", "Hello W") = 1 Then
    MsgBox "Yep, this string begins with Hello W!"
End If

If InStr returns 1, then the String ("Hello World"), begins with the substring ("Hello W").

Like

You can also use the like comparison operator along with some basic pattern matching:

If "Hello World" Like "Hello W*" Then
    MsgBox "Yep, this string begins with Hello W!"
End If

In this, we use an asterisk (*) to test if the String begins with our substring.

`InStr` does the job, but it doesn't seem to be a `Like` in VBScript (see https://stackoverflow.com/a/30301238/61508) — immitev, Feb 12 '20 at 11:00

score 49 · Answer 2 · answered Dec 27 '13 at 18:08

49

Judging by the declaration and description of the startsWith Java function, the "most straight forward way" to implement it in VBA would either be with Left:

Public Function startsWith(str As String, prefix As String) As Boolean
    startsWith = Left(str, Len(prefix)) = prefix
End Function

Or, if you want to have the offset parameter available, with Mid:

Public Function startsWith(str As String, prefix As String, Optional toffset As Integer = 0) As Boolean
    startsWith = Mid(str, toffset + 1, Len(prefix)) = prefix
End Function

answered Dec 27 '13 at 18:08

Blackhawk

5,984
4
27
56

5

This also has the bonus of being faster to execute than armstrhb's solution (his solution is however easier to read and code). – dan Dec 27 '13 at 20:24
...and to check if it ends with the substring, just use `Right()` instead: `Right(str, Len(prefix)) = prefix` :-) – Stephen R May 09 '19 at 17:50
2

You'll also speed it up a teeeeeny bit by using the Mid$(...) function or Left$(...) functions (which allow the string variable to be passed straight in without being wrapped up in a Variant) – Daniel Scott Dec 03 '19 at 08:47

score 3 · Answer 3 · answered Jan 03 '21 at 09:36

The best methods are already given but why not look at a couple of other methods for fun? Warning: these are more expensive methods but do serve in other circumstances.

The expensive regex method and the css attribute selector with starts with ^ operator

Option Explicit

Public Sub test()

    Debug.Print StartWithSubString("ab", "abc,d")

End Sub

Regex:

Public Function StartWithSubString(ByVal substring As String, ByVal testString As String) As Boolean
    'required reference Microsoft VBScript Regular Expressions
    Dim re As VBScript_RegExp_55.RegExp
    Set re = New VBScript_RegExp_55.RegExp

    re.Pattern = "^" & substring

    StartWithSubString = re.test(testString)

End Function

Css attribute selector with starts with operator

Public Function StartWithSubString(ByVal substring As String, ByVal testString As String) As Boolean
    'required reference Microsoft HTML Object Library
    Dim html As MSHTML.HTMLDocument
    Set html = New MSHTML.HTMLDocument

    html.body.innerHTML = "<div test=""" & testString & """></div>"

    StartWithSubString = html.querySelectorAll("[test^=" & substring & "]").Length > 0

End Function

Test if string begins with a string?

3 Answers3

InStr

Like

Linked