VBA String Functions

Below listed are Excel VBA String functions:


VBA String Functions

VBA Declare String

Dim str as String
str = "Hello there!" 'This is a String

Concatenate VBA Strings

Dim str1 as String, str2 as String, concatStr as String
concatStr = str1 & str2
concatStr = "Hello " & "there!"

Blank VBA Strings

Create Blank String:

Dim str as String
str = "" 'Empty String
str = vbNullString 'Empty String. Same as above but more efficient

Check if VBA String is Blank:

Dim str as String
'...
if str = vbNullString Then Debug.Print "String is Blank!"

VBA String Length

Dim str as String
str = "Hello" 
Debug.Print Len(str) 'Result: 5

Read more on the VBA Len function.

VBA Substrings Length

Dim str As String: str = "Hello there!"
Debug.Print Left(str, 5) 'Result: "Hello"
Debug.Print Mid(str, 7, 6) 'Result: "there!"
Debug.Print Right(str, 6) 'Result: "there!"
Debug.Print Split(str, " ")(0) 'Result: "Hello"

Read my post on using the Left, Right, Mid and Split functions in VBA

VBA Replace String

Dim str As String: str = "Hello there!"
Debug.Print Replace(str, "Hello", "You") 'Result: "You there!"

VBA Like Operator

If "Animal" Like "[A-Z]*" then 
   Debug.Print "String starting with Capital letter!"
End If

Read my post on using the VBA Like Operator.

VBA Find in String

If InStr("Hello There!","Hello") > 0 then 
   Debug.Print "Contains ""Hello"" Substring!"
End If

Read my post on Finding Substrings within Strings.

Trim VBA String

Triming removes Whitespaces:

Debug.Print Trim(" Hello There! ") 'Result: "Hello There!"
Debug.Print LTrim(" Hello There! ") 'Result: "Hello There! "
Debug.Print RTrim(" Hello There! ") 'Result: " Hello There!"