Excel VBA String Functions:
Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE, InStr & InStrRev Functions
-------------------------------------------------------
Contents:
LEFT Function (Worksheet / VBA)
RIGHT Function (Worksheet / VBA)
MID Function (Worksheet / VBA)
LEN Function (Worksheet / VBA)
InStr & InStrRev Functions (VBA)
-------------------------------------------------------
In Excel vba, a String refers to a sequence of contiguous characters within quotation marks viz. "This is a string expression within quotation marks, in vba." These characters are literally interpreted as characters, in the sense that these represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. A string expression can have as its elements - a string of contiguous characters, a function that returns a string, a string variable, a string constant or a string variant. This section does a detailed discussion on using Excel VBA String Functions to manipulate text strings with vba code. Also refer related link: Excel VBA String Functions: SPLIT, JOIN, CONCATENATE.
LEFT Function (Worksheet / VBA)
The Excel LEFT function can be used both as a worksheet function and a VBA function. The LEFT function returns the specified number of characters in a text string, starting from the first or left-most character. Use this function to extract a sub-string from the left part of a text string. Syntax: LEFT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the LEFT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.
RIGHT Function (Worksheet / VBA)
The Excel RIGHT function can be used both as a worksheet function and a VBA function. The RIGHT function returns the specified number of characters in a text string, starting from the last or right-most character. Use this function to extract a sub-string from the right part of a text string. Syntax: RIGHT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the RIGHT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.
MID Function (Worksheet / VBA)
The Excel MID function can be used both as a worksheet function and a VBA function. The MID function returns the specified number of characters in a text string, starting from a specified position (ie. starting from a specified character number). Use this function to extract a sub-string from any part of a text string. Syntax: MID(text_string, start_number, char_numbers). The text_string argument is the text string from which you want to extract the specified number of characters. The start_number argument specifies the character number from which to start extracting the sub-string, the first character in a text string being start_number 1 and incrementing towards the right. The char_numbers argument specifies the number of characters to extract from the text string.
If start_number is greater than the length of the text string, an empty string (zero length) is returned; if it is less than the length of the text string but together with char_numbers (ie. start_number PLUS char_numbers) it is greater than the length of the text string, the MID function will return the text string in full from the start_number position to the end of the text string.
Using MID function as a worksheet function, if a negative value is specified for char_numbers, MID will return the #VALUE! error value; if start_number is less than 1, MID will return the #VALUE! error value. All arguments are necessary to be specified when using as a worksheet function.
Using MID function as a VBA function: The char_numbers argument is optional when used as VBA function, and if omitted the function will return the text string in full from the start_number position to the end of the text string. All other arguments are necessary to be specified when using as a vba function. If text_string contains Null, the function also returns Null.
LEN Function (Worksheet / VBA)
The Excel LEN function can be used both as a worksheet function and a VBA function. The worksheet LEN function returns the number of characters in a text string. Use this function to get the length of a text string. Syntax: LEN(text_string). It is necessary to mention the text_string argument which is the text string whose length you want to get in number of characters. Note that spaces also count as characters. The worksheet LENB function returns the number of bytes used to represent the characters in a text string - counts each character as 1 byte except when a DBCS language [viz. Japanese, Chinese (Simplified), Chinese (Traditional), and Korean] is set as the default language wherein a character is counted as 2 bytes. Syntax: LENB(text_string).
While using LEN as a VBA function - Syntax: Len(text_string) or Len(variable_name) - you can use either a text string or a variable name, and the function will return a Long value representing the number of characters contained in the string or the number of bytes required to store a variable. Using the vba Len function for a variable of type variant will treat the variable as a String and return the number of characters contained in it. A text_string or variable containing Null, will also return Null. The vba Len function returns the number of characters in the string where the variable is of subtype String or Variant, and wherein the variable is of subtype numeric the function returns the number of bytes used to store the variable.
Example - Using Left, Right, Mid & Len functions in vba code.
Sub Left_Right_Mid_Len()
'using vba Left, Right, Mid & Len functions.
Dim str As String, strLeft As String, strRight As String, strMid As String
str = "James Bond"
strLeft = Left(str, 7)
'returns "James B", which are the first 7 characters (space is counted as a distinct character).
MsgBox strLeft
strLeft = Left(str, 15)
'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.
MsgBox strLeft
strRight = Right(str, 7)
'returns "es Bond", which are the last 7 characters (space is counted as a distinct character).
MsgBox strRight
strRight = Right(str, 15)
'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.
MsgBox strRight
strMid = Mid(str, 2, 6)
'Returns "ames B". Starts from the second character ie. "a", and then specifies that 6 characters be returned starting from "a".
MsgBox strMid
strMid = Mid(str, 2, 15)
'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the specified characters of 15 plus start number 2 (ie. total of 17) exceed the string length of 10 characters.
MsgBox strMid
strMid = Mid(str, 2)
'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the second argument (char_numbers) is omitted.
MsgBox strMid
strMid = Mid(str, 12, 2)
'Returns an empty string (zero length), because the start number of 12 exceeds the string length of 10 characters.
MsgBox strMid
'Returns 10, the string length measured by its number of characters.
MsgBox Len(str)
'Returns 10, the string length measured by its number of characters.
MsgBox Len("James Bond")
End Sub
Example - Using the vba Len function - variable types.
Sub Len_vbaFunc()
'using vba Len function - variable types
'---------------------------
'returns 3 in both cases - number of characters in the string:
MsgBox Len("bad")
MsgBox Len("245")
'returns 10 - number of characters in the string including the space:
MsgBox Len("James Bond")
'---------------------------
'a variable of type variant is treated as a string
Dim vVar As Variant
vVar = 245
'returns 2, indicating variable subtype Integer:
MsgBox VarType(vVar)
'Returns 3, the number of characters contained in the variable - the Len functions treats the variant variable as a String:
MsgBox Len(vVar)
'---------------------------
'a variable of type string
Dim strVar As String
strVar = "James Bond"
'returns 8, indicating variable subtype String:
MsgBox VarType(strVar)
'Returns 10, the number of characters contained in the variable of type String:
MsgBox Len(strVar)
'---------------------------
'a variable of type integer
Dim iVar As Integer
iVar = 245
'Returns 2, the number of bytes used to store the variable:
MsgBox Len(iVar)
'a variable of type long
Dim lVar As Long
lVar = 245
'Returns 4, the number of bytes used to store the variable:
MsgBox Len(lVar)
'a variable of type single
Dim sVar As Single
sVar = 245.567
'Returns 4, the number of bytes used to store the variable:
MsgBox Len(sVar)
'a variable of type double
Dim dVar As Double
dVar = 245.567
'Returns 8, the number of bytes used to store the variable:
MsgBox Len(dVar)
'---------------------------
End Sub
Example - Using LEN and MID Functions, to determine characters appearing at odd number positions in a text string.
End Sub
Example - Using LEFT, LEN and MID Functions, to return initials of full name.
'Using LEFT, LEN and MID Functions, to return initials of full name.
'return initials from a text string containing full name comprising of multiple words
'consider a string containing the first name, middle name(s) & surname, having inbetween spaces - return initials of full name, wherein each initial is followed by a dot and space.
Sheets("Sheet1").Activate
'loop though each character:
'convert all initials to upper case:
End Sub
The worksheet REPLACE function replaces part of a text string with a new text string, based on specified number of characters and starting from a specified position. Syntax: REPLACE(old_text, start_number, number_of_chars, new_text). It is necessary to specify all arguments. The old_text argument is the text string in which you want to replace with new text. The start_number argument is the position of the character in old_text, which you want to replace (ie. position of the first character from which replacement should start). Position is the character number, first character being number 1 & incrementing towards the right. The number_of_chars is the number of characters which will be replaced in the old_text (with new_text). The new_text is the text string which will replace the characters in old_text.
Example - using the Worksheet Replace Function in vba code to delete blank spaces in excess of a specified number, within a string
'using the Worksheet Replace Function in vba code to delete blank spaces in excess of a specified number, within a string (str)
'use this code to reduce multiple spaces within a string to a specified number of spaces (iSpaces)
'this code can also delete ALL spaces within a string
'this code can also convert multiple spaces to a single space within a string, similar to the worksheet Trim function, except that using Trim deletes ALL blank spaces before the first (non-space) character also.
Dim n As Integer, counter As Integer
'start from last character in the string and move to the first
'function returns the final string after deleting excess consecutive blank spaces
End Function
'delete excess consecutive blank spaces within a string (str) ie. in excess of a specified number
'Note: this code does not ensure uniform no. of blank spaces but ONLY deletes excess blanks
Sheets("Sheet1").Activate
'assign string in cell A10 (" Specify max spaces in text .") of active sheet to variable (strText)
'strText contains 4 blank spaces after "Specify", 4 blank spaces after "spaces" & 3 blank spaces after "text", all of which are reduced to 2 blank spaces
End Sub
'ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present
Dim n As Integer, counter As Integer
'start from last character in the string and move to the first
'function returns the final string with uniform consecutive blank spaces
End Function
'ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present
'Note: this code ensures uniform no. of blank spaces where lead & trailing spaces are also present
Sheets("Sheet1").Activate
'assign string in cell A10 (" Specify max spaces in text .") of active sheet to variable (strText)
'enter final string in cell A12 (" Specify max spaces in text .") - call UniformBlankSpaces function and pass the arguments of string & specified blank spaces
End Sub
The vba Replace Function is used to return a string in which a specified substring is replaced, a specified number of times, with another specified substring. Syntax: Replace(expression, find, replace, start, count, compare). It is necessary to specify the arguments of expression, find and replace, while the start, count & compare arguments are optional.
The expression argument is the string in which a specific substring is replaced. For a zero-length expression string a zero-length string is returned, and for a Null expression the function will give an error. The find argument specifies the substring which is to be replaced. If find substring is zero-length, then a copy of the expression is returned. The replace argument specifies that 'another substring' which replaces (ie. the replacement substring). A replace substring of zero-length has the effect of deleting all occurrences of find from the expression. The start argument specifies the position (ie. character number) in the expression from where you want to start your search for the 'find' substring. If omitted, it will default to1 (ie. search will start from first character position). The string returned by the Replace function begins from this start position till the last character of the expression string, after the replacement(s). Specifying a start position which is greater than the length of the expression, will return a zero-length string. The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements. Specifying zero for the count will have the effect of no replacement and will return a copy of the expression. The compare argument specifies the type of comparison to use for evaluating substrings - a numeric value or a constant can be specified herein, as detailed below.
You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.
Example - using the vba Replace function.
Sub Replace_vbaFunc()
'using the vba Replace function
Dim str As String, strFind As String, strReplace As String
'find all ocurrences of "s" to be replaced:
strFind = "s"
'set replace string as zero-length:
strReplace = ""
'---------------------------
str = "She was selling sea shells!"
'returns 27:
MsgBox Len(str)
'returns the string after deleting all occurences of 's' - "She wa elling ea hell!". Note that capital 'S' is not replaced.
str = Replace(str, strFind, strReplace)
MsgBox str
'returns 22:
MsgBox Len(str)
'---------------------------
str = "She was selling sea shells!"
'returns 27:
MsgBox Len(str)
'returns the string after deleting all occurences of 's' - "he wa elling ea hell!".
'Note that capital 'S' is also replaced because a textual comparison has been done by setting the compare argument value to vbTextCompare (value: 1).
str = Replace(str, strFind, strReplace, , , 1)
MsgBox str
'returns 21:
MsgBox Len(str)
'---------------------------
str = "She was selling sea shells!"
'returns 27:
MsgBox Len(str)
'deleting all occurences of 's' from start position 8 - returns " elling ea hell!". Note that the character number 8 is a blank space and this is also returned.
'the string returned by the Replace function begins from this start position till the last character of the expression string.
str = Replace(str, strFind, strReplace, 8)
MsgBox str
'returns 16:
MsgBox Len(str)
'---------------------------
str = "She was selling sea shells!"
'returns 27:
MsgBox Len(str)
'Specifying a start position which is greater than the length of the expression, will return a zero-length string.
str = Replace(str, strFind, strReplace, 28)
'returns a zero-length string:
MsgBox str
'returns 0:
MsgBox Len(str)
'---------------------------
str = "She was selling sea shells!"
'returns 27:
MsgBox Len(str)
'The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements.
'specifying a start position of 8, and count of 2:
str = Replace(str, strFind, strReplace, 8, 2)
'returns " elling ea shells!", after deleting 's' twice from start position of 8.
MsgBox str
'returns 18:
MsgBox Len(str)
'---------------------------
End Sub
Example - Manipulate Strings in Excel VBA - Remove Numericals, Delete/Add Blank Spaces, Capitalize letters.
To download Excel file with live code, click here.
'1. removes numericals from a text string;
'2. removes leading, trailing & inbetween spaces (leaves single space between words);
'3. adds space (if not present) after each exclamation, comma, full stop and question mark;
'4. capitalizes the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark;
Dim iTxtLen As Integer, iStrLen As Integer, n As Integer, i As Integer, ansiCode As Integer
'---------------------------
'The vba Chr function returns a character (string data type) identified to the specified character code.
'---------------------------
'use the worksheet TRIM function. Note: the TRIM function removes space character with ANSI code 32, does not remove the nonbreaking space character with ANSI code 160
'---------------------------
'set variable (iTxtLen) value to string length
'---------------------------
'reset variable value to string length:
'---------------------------
'capitalize the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark, while all other letters are lower case
'---------------------------
'manipulate string
Example - Replace all occurrences of a substring in a string expression, with another substring - using the vba Replace function.
To download Excel file with live code, click here.
Dim iPosn As Integer
'if vFind not found within var
End Function
'Replaces all occurrences of substring (vFind) in the string (var), with another substring (vReplace) - using the vba Replace function.
'this procedure calls Replace_Str1 function and passes the four argumentes of string (var) in which a specific substring (vFind) is replaced with another substring (vReplace) and the type of comparison (iOption) to use for evaluating substrings
Sheets("Sheet3").Activate
'var is the string within which vFind is searched & replaced by vReplace:
'Null: where a variable contains no valid data - Null may have been explicitly assigned to a variable, or it could be a result of an operation between expressions containing Null.
End Sub
InStr & InStrRev Functions (VBA)
The InStr Function returns the position (character number) in which a string first occurs within another string. Syntax: InStr(start, string, substring, compare). It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional.
The start argument specifies the position (ie. character number) within string from where you want to start your search for the substring. It is necessary to specify the start argument, if the compare argument is to be specified. If omitted, it will default to1 (ie. search will start from the first character position). Specifying a start position which is greater than the length of string will return a 0 (zero), and if start contains Null an error will occur. The string argument is the string expression within which substring is being searched. The function returns 0 if string is zero-length, and returns Null if string is Null. The substring argument is the string expression which is being searched within string and whose position will be returned by the function. The function returns 0 if substring is not found, returns the start value if string is zero-length, and returns Null if substring is Null. The compare argument specifies the type of comparison to use for evaluating strings - a numeric value or a constant can be specified herein, as detailed below.
You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.
The InStrRev Function returns the position (character number) in which a string first occurs within another string, starting from the end of that another string. Syntax: InStrRev(string, substring, start, compare). Use InStrRev function instead of InStr to search in the reverse direction. It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional. If start is omitted, -1 is used, meaning that the search will begin from the last character position. All other syntax explanations remain same as in the InStr function.
Example - Using the InStr function.
Sub InStrFunc()
'using the InStr function
Dim str1 As String, str2 As String
'-----------------
str1 = "she sells sea shells"
str2 = "e"
'returns 3:
MsgBox InStr(str1, str2)
'-----------------
str1 = "she sells sea shells"
str2 = "e"
'returns 6:
MsgBox InStr(4, str1, str2)
'-----------------
str1 = "she sells sea shells"
str2 = "e"
'returns 0 because start value (24) is greater than length of str1 (20):
MsgBox InStr(24, str1, str2)
'-----------------
str1 = ""
str2 = "e"
'returns 0 because str1 is zero-length:
MsgBox InStr(str1, str2)
'-----------------
str1 = "she sells sea shells"
str2 = "f"
'returns 0 because str2 is not found:
MsgBox InStr(str1, str2)
'-----------------
Dim str3 As Variant
str1 = "she sells sea shells"
str3 = Null
'returns 1, indicating subtype Null - because str3 is Null:
MsgBox VarType(InStr(str1, str3))
'-----------------
str1 = "she Sells sea shells"
str2 = "s"
'returns 9 - the default text comparison type is Binary (case-sensitive):
MsgBox InStr(2, str1, str2)
'returns 5 - performing a textual comparison wherein comparison type is not case-sensitive:
MsgBox InStr(2, str1, str2, 1)
'-----------------
End Sub
Example - Replace all occurrences of a substring in a string expression, with another substring - using the InStr, Left & Mid functions.
To download Excel file with live code, click here.
'Replace all occurrences of a substring in a string expression, with another substring - using the InStr, Left & Mid functions.
'Replaces all occurrences of vFind in var, with vReplace - using the InStr, Left & Mid functions.
'Replacement will be case-sensitive in this procedure ie. if you specify to replace occurrences of "a" then instances of "A" will NOT be replaced.
Dim iFindLen As Integer
'position of the first occurrence of vFind, within var:
'if vFind not found within var:
'return final string:
End Function
'Replaces all occurrences of vFind in var, with vReplace - using the InStr, Left & Mid functions.
'Replacement will be case-sensitive in this procedure ie. if you specify to replace occurrences of "a" then instances of "A" will NOT be replaced.
Sheets("Sheet3").Activate
'var is the string within which vFind is searched & replaced by vReplace:
'if var is Null, exit procedure:
End Sub
