Excel VBA – String Manipulation in Excel: In this chapter, you’ll find the most important functions to manipulate strings in Excel VBA.
String Manipulation Functions
Excel VBA String Manipulation is an important element in Excel VBA for Macro programming and string operations. Excel provides various types of VBA String Manipulation functions like:
- Join: is used to concatenate or join two strings
- Left: is used to extract characters from the left side of the string
- Right: is used to extract characters from the right side of the string
- Mid: is used to extract a substring from the middle of a string
- Len: is used to find the length of a string
- Instr: is used to find the position of a substring in a string
Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.
Join Strings
We use the & operator to concatenate (join) strings.
Code:
| Dim text1 As String, text2 As String text1 = “Hi” text2 = “Tim”MsgBox text1 & ” ” & text2 |
Result:
Note: to insert a space, use ” “
String Manipulation: Left
To extract the leftmost characters from a string, use Left.
Code:
| Dim text As String text = “example text” |
MsgBox Left(text, 4)
Result:
String Manipulation: Right
To extract the rightmost characters from a string, use Right. We can also directly insert text in a function.
Code:
| MsgBox Right(“example text”, 2) |
Result:
String Manipulation: Mid
To extract a substring, starting in the middle of a string, use Mid.
Code:
| MsgBox Mid(“example text”, 9, 2) |
Result:
Note: started at position 9 (t) with length 2. You can omit the third argument if you want to extract a substring starting in the middle of a string, until the end of the string.
Len
To get the length of a string, use Len.
Code:
| MsgBox Len(“example text”) |
Result:
Note: space (position 8) included!
Instr
To find the position of a substring in a string, use Instr.
Code:
| MsgBox Instr(“example text”, “am”) |
Result:
Note: string “am” found at position 3. Visit our page about the Instr function for more information and examples.
Conclusion
Concatenation
- &: This operator is used to combine two or more strings into one. It effectively joins strings together.
Finding Substrings
- InStr: You use this function to find the position of a substring within another string. It returns the position (as an integer) of the first occurrence of the substring. You can specify the starting position for the search and whether the search is case-sensitive.
Extracting Substrings
- Mid: This function extracts a portion of a string from a given starting position for a specified length. It is useful for retrieving specific parts of a string.
Changing Case
- UCase: This function converts all the characters in a string to uppercase.
- LCase: This function converts all the characters in a string to lowercase.
Replacing Substrings
- Replace: This function replaces occurrences of a specified substring within a string with another substring. You can specify the starting position, the number of occurrences to replace, and whether the replacement is case-sensitive.
Trimming Whitespace
- Trim: This function removes leading and trailing spaces from a string. It does not remove spaces within the string.
String Length
- Len: This function returns the length of a string (the number of characters it contains). It is useful for determining how many characters are in a string.
These functions and operators provide a robust set of tools for manipulating and working with strings in Excel VBA, allowing you to perform various tasks such as combining, searching, extracting, converting, replacing, and measuring strings.
| Next Chapter: Date and Time |
