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
- String - Create a repeating character string of the length specified.
- 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
str = Left(String:="Hello World", Length:=1) ' Returns "H".
str = Left(String:="Hello World", Length:=7) ' Returns "Hello W".
str = Left(String:="Hello World", Length:=20) ' ' Returns "Hello World".
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://learn.microsoft.com/en-gb/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.
str = Right(String:="Hello World", Length:=1) ' Returns "d".
str = Right(String:=str"Hello World", Length:=6) ' Returns " World".
str = Right(String:="Hello World", Length:=20) ' Returns "Hello World".
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://learn.microsoft.com/en-gb/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.'
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
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.'
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.
str = Replace(Expression:="alphabet", Find:="bet", Replace:="hydro") 'Result: "alphahydro"
str = Replace(Expression:="alphabet", Find:="a", Replace:="x") 'Result: "xlphxbet"
str = Replace(Expression:="alphabet", Find:="a", Replace:="x", Start:=2) 'Result: "lphxbet"
str = Replace(Expression:="alphabet", Find:="a", Replace:="x", Start:=1, Count:=1) 'Result: "xlphabet"
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.
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.
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.
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.
str = Format(Expression:=#5:04:23 PM#, Format:="h:m:s") ' Returns "17:4:23".
str = Format(Expression:=#5:04:23 PM#, Format:="hh:mm:ss am/pm") ' Returns "05:04:23 pm".
str = Format(Expression:=#5:04:23 PM#, Format:="hh:mm:ss AM/PM") ' Returns "05:04:23 PM".
str = Format(Expression:=#1/27/2023#, Format:="dddd, mmm d yyyy")' Returns "Friday, Jan 27 2023".
str = Format(23) ' Returns "23".' ' If a format is not supplied, a string is returned.
Further, User-defined formats:
str = Format(Expression:=5459.4, Format:="##,##0.00") ' Returns "5,459.40".
str = Format(Expression:=334.9, Format:="###0.00") ' Returns "334.90".
str = Format(Expression:=5, Format:="0.00%") ' Returns "500.00%".
str = Format(Expression:="HELLO", Format:="<") ' Returns all lowercase: "hello".
str = 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.
strLowerCase = LCase("Hello World 1234") ' Returns "hello world 1234".
Ucase - Converts string to uppercase
The Ucase function returns a String containing the specified string, converted to uppercase.
strUpperCase = UCase("Hello World 1234") ' Returns "HELLO WORLD 1234".
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"
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(SourceArray:= dirs, Delimiter:= "\") '"C:\My Documents\DataFiles"
String - Create a repeating character string of the length specified
str = String(Number:=", Character:="A") ' Returns "AAAAA".
Space - Make a String consisting of a number of spaces.
The Space function returns a String consisting of the specified number of spaces.
str = "Good" & Space(3) & "Morning" ' Returns "Good Morning".
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://learn.microsoft.com/en-gb/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.
MyChar = Chr(65) ' Returns A.
MyChar = Chr(97) ' Returns a.'
MyChar = Chr(62) ' Returns >.'
MyChar = Chr(37) ' Returns %.'
See https://learn.microsoft.com/en-gb/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.
str = StrReverse("123ab") ' Result: "ba321"
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
str = 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
str = MonthName(Month:=1) ' returns "January"