Below listed are Excel VBA String functions:
VBA Declare String
1 2 | Dim str as Stringstr = "Hello there!" 'This is a String |
Concatenate VBA Strings
1 2 3 | Dim str1 as String, str2 as String, concatStr as StringconcatStr = str1 & str2concatStr = "Hello " & "there!" |
Blank VBA Strings
Create Blank String:
1 2 3 | Dim str as Stringstr = "" 'Empty Stringstr = vbNullString 'Empty String. Same as above but more efficient |
Check if VBA String is Blank:
1 2 3 | Dim str as String'...if str = vbNullString Then Debug.Print "String is Blank!" |
VBA String Length
1 2 3 | Dim str as Stringstr = "Hello" Debug.Print Len(str) 'Result: 5 |
Read more on the VBA Len function.
VBA Substrings Length
1 2 3 4 5 | 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
1 2 | Dim str As String: str = "Hello there!"Debug.Print Replace(str, "Hello", "You") 'Result: "You there!" |
VBA Like Operator
1 2 3 | 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
1 2 3 | 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:
1 2 3 | Debug.Print Trim(" Hello There! ") 'Result: "Hello There!"Debug.Print LTrim(" Hello There! ") 'Result: "Hello There! "Debug.Print RTrim(" Hello There! ") 'Result: " Hello There!" |
