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.

Below image shows the Code VBA add-in support for VBA String procedures.

menu with string functions.

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".'

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

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".'

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.

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"'

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->".'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/ltrim-rtrim-and-trim-functions

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"'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatnumber-function

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)".'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatcurrency-function

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".'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatdatetime-function

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".'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatdatetime-function

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%)".'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatpercent-function

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.'
 ' 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".'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications

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".'

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.

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.


str = "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.

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.

str = 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 » 

str = WeekdayName(Weekday:=1) ' returns "Sunday"

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/weekdayname-function

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"

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/monthname-function