Strings - VBA

Strings is a variable data type which is combination of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string data type if it is enclosed within double quotes .

Declaration and Syntax

dim variable as String

variable = " this is string"

String Functions

Function Name	Description
InStr	Returns the first occurrence of the specified substring. Search happens from the left to the right.
InstrRev	Returns the first occurrence of the specified substring. Search happens from the right to the left.
Lcase	Returns the lower case of the specified string.
Ucase	Returns the upper case of the specified string.
Ltrim	Returns a string after removing the spaces on the left side of the specified string.
Rtrim	Returns a string after removing the spaces on the right side of the specified string.
Trim	Returns a string value after removing both the leading and the trailing blank spaces.
Replace	Returns a string after replacing a string with another string.
Space	Fills a string with the specified number of spaces.
StrComp	Returns an integer value after comparing the two specified strings.
String	Returns a string with a specified character for specified number of times.
StrReverse	Returns a string after reversing the sequence of the characters of the given string.
Left	Returns a specific number of characters from the left side of the string.
Right	Returns a specific number of characters from the right side of the string.
Mid	Returns a specific number of characters from a string based on the specified parameters.
Len	Returns the length of the given string.

Example

InStr

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox InStr(s, "the") End Sub

Result

return the position of "the" in the string

InstrRev

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox InStrRev(s, "the") End Sub

Result

return the position of "the" in the reverse string

Lcase

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox LCase(s) End Sub

Result

return the string in lower case

Ucase

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox UCase(s) End Sub

Result

return the string in UpperCase

Ltrim

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox LTrim(s) End Sub

Result

trim space from the left side of the string 

Rtrim

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox RTrim(s) End Sub

Result

trim space from the right side of the string 

Trim	Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Trim(s) End Sub

Result

Returns a string value after removing both the leading and the trailing blank spaces.

Replace

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Replace(s, "the", "with") End Sub

Result

return the position of "the" with the value "with"

Space

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Space(4) End Sub

Result

replace the current string with the "" blank space

StrComp

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox StrComp(s, s2, vbTextCompare) End Sub

Result

compare sring1 with string2 and return value if matched or not 

StrReverse

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox StrReverse(s) End Sub

Result

return the string in reverse order

Left	Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Left(s, 5) End Sub

Result

return the 5 character from the left side of the string

  

Right

Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Right(s, 5)

Result

return the 5 character from the right side of the string 

Mid	Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Mid(s, 5, 8) End Sub

Result

return the 8 character from the string staring position 5th character

 

Len	Sub stringmanupulation() Dim s As String Dim s2 As String s = " this is the complete string for the demo" s2 = "strig for compare" MsgBox Len(s) End Sub

Result

return the length of the string string