Learn about VBA String Function

Learn about VBA STRING Function

Excel VBA provides a variety of string functions that help manipulate and analyze text. Here are the commonly used VBA string functions with examples:

1. Len (Length of a String)

• Purpose: Return the number of a characters in a string (including space)
• Syntax: Len(String)

Example:

Sub string_1()

Dim str As Variant

str = "Hello! I am VBA"

MsgBox Len(str)

'out put result will be 15

End Sub

2. Left (Extract Leftmost Character)

• Purpose: Extract a specified number of a characters from the beginning of a string.
• Syntax: Left(string, length)

Example:

Sub String_2()

Dim str As Variant

str = "Hello! I am VBA"

MsgBox Left(str, 5)

'out put result will be "Hello"

End Sub

3. Right (Extract Rightmost Characters)

• Purpose: Extract a specified number of character from the end of a string.
• Syntax: Right(String , Length)

Example:

Sub String_3()

Dim str As Variant

str = "Hello! I am VBA"

MsgBox Right(str, 3)

'out put result will be "VBA"

End Sub

4. Mid (Extract Substring)

• Purpose: Extracts a substring from a string starting at a specified position
• Syntax: (String, Start, Length)

Example:

Sub String_4()

Dim str As Variant

str = "Hello! I am VBA"

MsgBox Mid(str, 8, 5)

'out put result will be "I am"

End Sub

5. InStr (Find Substring Position)

• What it does: Finds where a specific word or character starts in a string.
• When to use: To check if a string contains a certain word or find its position.
• Syntax: InStr( [strart], string1, string2, [compare])

Example:

Sub String_5()

Dim str As Variant

str = "Hello! I am VBA"

MsgBox InStr(1, str, "VBA")

'out put result will be 13

End Sub

6. Replace (Replace Substring)

• What it does: Replaces one word or phrase with another.
• When to use: To fix errors or clean up text.
• Syntax: Replace(expression, find, replace, [start] , [count], [compare])

Example:

Sub String_6()

Dim str As Variant

str = "Hello! I am World "

MsgBox Replace(str, "world", "VBA")

'out put result will be "Hello! I am VBA"

End Sub

7. UCase (Convert to Uppercase)

• Purpose: Convert String in Uppercase
• Syntax: Ucase(String)

Example:

Sub String_7()

Dim str As Variant

str = "Hello! I am VBA

MsgBox UCase(str)

'out put result will be "HELLO! I AM VBA"

End Sub

8. LCase (Convert to Lowercase)

• Purpose: Convert String in Lowercase
• Syntax: LCase(String)

Example:

Sub String_8()

Dim str As Variant

str = "Hello! I am VBA "

MsgBox LCase(str)

'out put result will be "hello! i am vba"

End Sub

9. Trim (Remove Leading and Trailing Spaces)

• Purpose: Removes extra spaces at the start and end of a string.
• Syntax: Trim(String)

Example

Sub String_9()

Dim str As Variant

str = " Hello VBA "

MsgBox Trim(str)

'out put result will be "Hello VBA"

End Sub

10. Space:

Use: The Space function fills a string with a specific number of spaces

Syntax: Space(string)

Example:

Sub String_10()

Dim str As String

str = "Vijay"

MsgBox str & Space(3) & str

'This function is use for give space

'out put result will be "Vijay Vijay"

End Sub

11. String:

Use : Creates a string with a specified character repeated a certain number of times.

Syntax: String(number,character)

Number − A required parameter. An integer value, which would be repeated for a specified number of times against the character parameter.

Character − A required parameter. Character value, which has to be repeated for a specified number of times.

Sub StringExample() MsgBox String(10, “A”) ‘ Result will be : AAAAAAAAAA End Sub

Excel file for Download

For More Understand we can visit on following

1. Excel VBA InStr
2. Excel VBA Objects