VBA String functions in Excel
In this topic, The various functions used to manipulate and work with strings are discussed.
The string functions are also most often used functions irrespective of size and complexity of the program.
For instance, if a full name of a person from a sheet is required, then the first name and the last name has to be combined and returned as a single name. This is the most basic usage of string function, called "concatenation".
There are also other string functions such as truncation of characters on either side, conversion of numbers to strings and so on. These functions are discussed as below
1.& Operation
This is string concatenation wherein two or more strings are combined to form a single string.
2.CStr()
This function is used to convert other data types, mostly numbers, to string data type.
3.Left() and Right()
Left(string,no_of charecters)
Right(string,no_of charecters)
Theses functions return a certain number of charecters from the string from either side as specified, upto the number of characters specified.
4.Ucase() and Lcase()
These functions are used to convert a given string to upper or lower case.
5.InStr()
This function is used to find if a specific string is found in some other string.
The following example illustrates these functions
- Sub str_func()
- Dim str As String
- str = "hello"
- MsgBox str & " in upper case: " & UCase(str)
- MsgBox UCase(str) & " in lower case: " & LCase(str)
- MsgBox "Concatenation of " & UCase(str) & " Excel: " & UCase(str) & " Excel"
- MsgBox "First 3 charecters from " & str & ": " & Left(str, 3)
- MsgBox "Last 3 charecters from " & str & ": " & Right(str, 3)
- MsgBox "If hel is present in " & str & "?" & vbNewLine & _
- InStr(str, "hel")
- End Sub
The screenshot of VBA editor
Output
