String functions in VBA
This page gives an overview of the VBA String functions with example code showing how they are used. A link is provided for each function to its documentation on microsoft learn VBA site.
- Left - Gets a left part of the string
- Mid - Gets a part of the string
- Right - Gets a right part of the string
- Len - Length of the string
- InStr - the position of the first occurrence of one string within another
- InStrRev - position of an occurrence-, from the end of the string
- strComp - Compares strings with nuances
- Replace - Returns a string in which a specified substring has been replaced
- Trim - Removes leading and/or trailing spaces
- Format - Format a String according to instructions
- Lcase - Converts string to lowercase
- Ucase - Converts string to uppercase
- Split - Returns a array containing substrings
- Join -Joins a number of strings contained in an array
- Space - Make a String consisting of a number of spaces
- Asc - Returns the character code a letter
- Chr - Returns a letter of a character code
- StrReverse - Reverses the character order of a string
- WeekdayName - The name of the day of the week
- MonthName - The name of the month.
Below image shows the Code VBA add-in support for VBA String procedures.
.
Hovering a menu you see an explanation what a procedure does, and what the inserted code will look like, depending on changes you make in the Function Call Builder dialog. Note that Code VBA IntelliSense also makes these function accessible under submenu VBA.
Left - Gets a left part of the string
The Left function returns a String containing a specified number of characters from the left side of a string.
From the Code VBA toolbar, select VBA > String> Left »
Dim MyStr As String
MyStr = Left(String:="Hello World", Length:=1) ' Returns "H".'
MyStr = Left(String:="Hello World", Length:=7) ' Returns "Hello W".'
MyStr = Left(String:="Hello World", Length:=20) ' ' Returns "Hello World".'See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/left-function
Mid - Gets a part of the string
The Mid function returns a String containing a specified number of characters from a string.
FirstWord = Mid("Mid Function Demo", Start:=1, Length:=3) ' Returns "Mid".'
LastWord = Mid("Mid Function Demo", Start:=14, Length:=4) ' Returns "Demo".'
MidWords = Mid("Mid Function Demo", Start:=5) ' Returns "Function Demo".'See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/mid-function
Right - Gets a right part of the string
The Right function returns a String containing a specified number of characters from the right side of a string.
Dim MyStr As String
MyStr = Right(String:="Hello World", Length:=1) ' Returns "d".'
MyStr = Right(String:=str"Hello World", Length:=6) ' Returns " World".'
MyStr = Right(String:="Hello World", Length:=20) ' Returns "Hello World".'See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/right-function
Len - Length of the string
The Len function returns a Long containing the number of characters in a string or the number of bytes required to store a variable.
Dim lngLen As Long
lngLen = Len("Hello World") 'returns 11'
lngLen = Len(FormatCurrency(123)) '$123.00, returns 7'See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/len-function
InStr - the position of the first occurrence of one string within another
The InStr function returns a Long specifying the position of the first occurrence of one string within another.
Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.'
SearchChar = "P" ' Search for "P".'
' A textual comparison starting at position 4. Returns 6.'
MyPos = Instr(4, SearchString, SearchChar, 1)
' A binary comparison starting at position 1. Returns 9.'
MyPos = Instr(1, SearchString, SearchChar, 0)
' Comparison is binary by default (last argument is omitted).'
MyPos = Instr(SearchString, SearchChar) ' Returns 9.'
MyPos = Instr(1, SearchString, "W") ' Returns 0.'See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/instr-function
InStrRev - position of an occurrence of one string within another, from the end of the string
The InStrRev function returns the position of an occurrence of one string within another, from the end of the string. Can only be used in Excel.
lng = InStrRev(StringCheck:="alphabet", StringMatch:="a") ' returns 5
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/instrrev-function
StrComp - Comparison of two strings
The StrComp function returns a Integer indicating the result of a string comparison.
From the Code VBA toolbar, select VBA > String> StrComp »
'vbUseCompareOption -1 Performs a comparison by using the setting of the Option Compare statement.'
MyComp = StrComp("ABCD", "abcd", vbTextCompare) ' vbTextCompare or 1, Returns 0.'
MyComp = StrComp("ABCD", "abcd", vbBinaryCompare) ' vbBinaryCompare or 0, Returns -1.'
MyComp = StrComp("abcd", "ABCD") ' Returns 1.'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/strcomp-function
Replace - Returns a string in which a specified substring has been replaced
The Replace function returns a string, which is a substring of a string expression beginning at the start position (defaults to 1), in which a specified substring has been replaced with another substring a specified number of times.
MyStr = Replace(Expression:="alphabet", Find:="bet", Replace:="hydro") 'Result: "alphahydro" '
MyStr = Replace(Expression:="alphabet", Find:="a", Replace:="x") 'Result: "xlphxbet"'
MyStr = Replace(Expression:="alphabet", Find:="a", Replace:="x", Start:=2) 'Result: "lphxbet"'
MyStr = Replace(Expression:="alphabet", Find:="a", Replace:="x", Start:=1, Count:=1) 'Result: "xlphabet"'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/replace-function
Trim - Removes leading and/or trailing spaces
The Trim function returns a String containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).
TrimString = LTrim(" <-Trim-> ") ' TrimString = "<-Trim-> ".'
TrimString = RTrim(" <-Trim-> ") ' TrimString = " <-Trim->".'
TrimString = LTrim(" <-Trim-> ") ' TrimString = "<-Trim->".'
' Using the Trim function alone achieves the same result.'
TrimString = Trim(" <-Trim-> ") ' TrimString = "<-Trim->".'
FormatNumber -
The FormatNumber function returns an expression formatted as a number.
From the Code VBA toolbar, select VBA > String> FormatNumber »
strFormattedNumber = FormatNumber(Expression:=1000000) '"1,000,000.00"'
strFormattedNumber = FormatNumber(Expression:=1000000, GroupDigits:=vbFalse) '"1000000.00"'
strFormattedNumber = FormatNumber(Expression:=-500, UseParensForNegativeNumbers:=vbTrue) '"(500.00)"'
strFormattedNumber = FormatNumber(Expression:=100.55, NumDigitsAfterDecimal:=0) '"101"'
FormatCurrency -
The FormatCurrency function returns an expression formatted as a currency value by using the currency symbol defined in the system control panel.
From the Code VBA toolbar, select VBA > String> FormatCurrency »
cur1 = FormatCurrency(Expression:=1000000#) ' "$1,000,000.00".'
cur2 = FormatCurrency(Expression:=1000000#, GroupDigits:=vbFalse) ' "$1000000.00".'
cur3 = FormatCurrency(Expression:=100.55, NumDigitsAfterDecimal:=0) ' "$101".'
cur4 = FormatCurrency(Expression:=-500, NumDigitsAfterDecimal:=2, UseParensForNegativeNumbers:=vbTrue) ' "($500.00)".'
FormatDate -
The FormatDate function returns an expression formatted as a date or time.
From the Code VBA toolbar, select VBA > String> FormatDate »
dat1 = FormatDateTime(Expression:=#1/1/2023#) ' "1/1/2023".'
dat2 = FormatDateTime(Expression:=#1/1/2023#, NamedFormat:=vbLongDate) ' "Sunday, January 01, 2023".'
dat3 = FormatDateTime(Expression:=#1/1/2023#, NamedFormat:=vbShortDate) ' "1/1/2023".'
FormatTime -
The FormatTime function returns an expression formatted as a date or time.
From the Code VBA toolbar, select VBA > String> FormatTime »
tim1 = FormatDateTime(Expression:=#12:00:00 PM#) ' tim1 is now equal to the String "12:00:00 PM".'
tim2 = FormatDateTime(Expression:=#12:00:00 PM#, NamedFormat:=vbLongTime) ' tim2 is now equal to the String "12:00:00 PM".'
tim3 = FormatDateTime(Expression:=#12:00:00 PM#, NamedFormat:=vbShortTime) ' tim3 is now equal to the String "12:00".'
FormatPercent -
The FormatPercent function returns an expression formatted as a percentage (multipled by 100) with a trailing % character.
From the Code VBA toolbar, select VBA > String> FormatPercent »
pc1 = FormatPercent(Expression:=10) ' "1,000.00%".'
pc2 = FormatPercent(Expression:=10, GroupDigits:=vbFalse) ' p "1000.00%".'
pc3 = FormatPercent(Expression:=0.559, NumDigitsAfterDecimal:=0) ' "56%".'
pc4 = FormatPercent(Expression:=-0.5, UseParensForNegativeNumbers:=vbTrue) ' "(50.00%)".'
Format - Returns a String formmated according to instructions contained in a format expression
The Format function returns a String containing an expression formatted according to instructions contained in a format expression.
MyStr = Format(Expression:=#5:04:23 PM#, Format:="h:m:s") ' Returns "17:4:23".'
MyStr = Format(Expression:=#5:04:23 PM#, Format:="hh:mm:ss am/pm") ' Returns "05:04:23 pm".'
MyStr = Format(Expression:=#5:04:23 PM#, Format:="hh:mm:ss AM/PM") ' Returns "05:04:23 PM".'
MyStr = Format(Expression:=#1/27/2023#, Format:="dddd, mmm d yyyy") ' Returns "Friday, Jan 27 2023".'
MyStr = Format(23) ' Returns "23".' ' If a format is not supplied, a string is returned.'
' User-defined formats.'
MyStr = Format(Expression:=5459.4, Format:="##,##0.00") ' Returns "5,459.40".'
MyStr = Format(Expression:=334.9, Format:="###0.00") ' Returns "334.90".'
MyStr = Format(Expression:=5, Format:="0.00%") ' Returns "500.00%".'
MyStr = Format(Expression:="HELLO", Format:="<") ' Returns all lowercase: "hello".'
MyStr = Format(Expression:="This is it", Format:=">") ' Returns all uppercase "THIS IS IT".'
Lcase - Converts string to lowercase
The Lcase function returns a String that has been converted to lowercase.
From the Code VBA toolbar, select VBA > String> Lcase »
strLowerCase = LCase("Hello World 1234") ' Returns "hello world 1234".'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/lcase-function
Ucase - Converts string to uppercase
The Ucase function returns a String containing the specified string, converted to uppercase.
From the Code VBA toolbar, select VBA > String> Ucase »
strUpperCase = UCase("Hello World 1234") ' Returns "HELLO WORLD 1234".'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/ucase-function
Split - Returns an array containing a substrings
The Split function returns a zero-based, one-dimensional array containing a specified number of substrings.
strArr = Split(Expression:="Hello, to, the, World", delimiter:=",")
' strArr(1) = "to"'
See https://learn.microsoft.com/office/vba/language/reference/user-interface-help/split-function
Join - Joins a number of strings contained in an array
The Join function returns a string created by joining a number of substrings contained in an array.
Dim dirs(0 To 2) As String
dirs(0) = "C:"
dirs(1) = "My Documents"
dirs(2) = "DataFiles"
Dim fullPath As String: fullPath = Join(dirs, "\") '"C:\My Documents\DataFiles" '
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/join-function
Space - Make a String consisting of a number of spaces.
The Space function returns a String consisting of the specified number of spaces.
MyString = "Good" & Space(3) & "Morning" ' Returns "Good Morning".'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/space-function
Asc - Returns the character code a letter
The Asc function returns an Integer representing the the ASCII value of a character or the first character in a string.
From the Code VBA toolbar, select VBA > String> Asc »
Dim MyNumber
MyNumber = Asc("A") ' Returns 65.'="time-functions-in-vba.htm
MyNumber = Asc("a") ' Returns 97.'
MyNumber = Asc("Apple") ' Returns 65, the first character.'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/asc-function
Chr - Returns a letter of a character code
The Chr function returns a String containing the character based on the ASCII value.
From the Code VBA toolbar, select VBA > String> Chr »
MyChar = Chr(65) ' Returns A.'
MyChar = Chr(97) ' Returns a.'
MyChar = Chr(62) ' Returns >.'
MyChar = Chr(37) ' Returns %.'
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/chr-function
StrReverse - Reverses the character order of a string
The StrReverse function returns a string in which the character order of a specified string is reversed.
From the Code VBA toolbar, select VBA > String> StrReverse »
MyString = StrReverse("123ab") ' Result: "ba321" '
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/strreverse-function
WeekdayName - The name of the day of the week
The WeekdayName function returns a string indicating the specified day of the week.
From the Code VBA toolbar, select VBA > String> WeekdayName »
MyString = WeekdayName(Weekday:=1) ' returns "Sunday"
MonthName - The name of the month.
The MonthName function returns a string indicating the specified month.
From the Code VBA toolbar, select VBA > String> MonthName »
MyString = MonthName(Month:=1) ' returns "January"
See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/monthname-function