Contents
- 1 Quick Guide to String Functions
- 2 The Webinar
- 3 Introduction
- 4 Read This First!
- 5 Appending Strings
- 6 Extracting Part of a String
- 7 Searching Within a String
- 8 Removing Blanks
- 9 Length of a String
- 10 Reversing a String
- 11 Comparing Strings
- 12 Comparing Strings using Pattern Matching
- 13 Replace Part of a String
- 14 Convert Types to String(Basic)
- 15 Convert String to Number-Â CLng, CDbl, Val etc.
- 16 Generate a String of items – String Function
- 17 Convert Case/Unicode – StrConv, UCase, LCase
- 18 Using Strings With Arrays
- 19 Formatting a String
- 20 Conclusion
- 21 What’s Next?
Quick Guide to String Functions
String operations | Function(s) |
---|---|
Append two or more strings | Format or "&" |
Build a string from an array | Join |
Compare - normal | StrComp or "=" |
Compare - pattern | Like |
Convert to a string | CStr, Str |
Convert string to date | Simple: CDate Advanced: Format |
Convert string to number | Simple: CLng, CInt, CDbl, Val Advanced: Format |
Convert to unicode, wide, narrow | StrConv |
Convert to upper/lower case | StrConv, UCase, LCase |
Extract part of a string | Left, Right, Mid |
Format a string | Format |
Find characters in a string | InStr, InStrRev |
Generate a string | String |
Get length of a string | Len |
Remove blanks | LTrim, RTrim, Trim |
Replace part of a string | Replace |
Reverse a string | StrReverse |
Parse string to array | Split |
The Webinar
If you are a member of the website, click on the image below to view the webinar for this post.
(Note: Website members have access to the full webinar archive.)
Introduction
Using strings is a very important part of VBA. There are many types of manipulation you may wish to do with strings. These include tasks such as
- extracting part of a string
- comparing strings
- converting numbers to a string
- formatting a date to include weekday
- finding a character in a string
- removing blanks
- parsing to an array
- and so on
The good news is that VBA contains plenty of functions to help you perform these tasks with ease.
This post provides an in-depth guide to using string in VBA. It explains strings in simple terms with clear code examples. I have laid it out so the post can be easily used as a quick reference guide.
If you are going to use strings a lot then I recommend you read the first section as it applies to a lot of the functions. Otherwise you can read in order or just go to the section you require.
Read This First!
The following two points are very important when dealing with VBA string functions.
The Original String is not Changed
An important point to remember is that the VBA string functions do not change the original string. They return a new string with the changes the function made. If you want to change the original string you simply assign the result to the original string. See the section Extracting Part of a String for examples of this.
How To Use Compare
Some of the string functions such as StrComp() and Instr() etc. have an optional Compare parameter. This works as follows:
vbTextCompare: Upper and lower case are considered the same
vbBinaryCompare: Upper and lower case are considered different
The following code uses the string comparison function StrComp() to demonstrate the Compare parameter
' https://excelmacromastery.com/ Sub Comp1() ' Prints 0 : Strings match Debug.Print StrComp("ABC", "abc", vbTextCompare) ' Prints -1 : Strings do not match Debug.Print StrComp("ABC", "abc", vbBinaryCompare) End Sub
You can use the Option Compare setting instead of having to use this parameter each time. Option Compare is set at the top of a Module. Any function that uses the Compare parameter will take this setting as the default. The two ways to use Option Compare are:
1. Option Compare Text: makes vbTextCompare the default Compare argument
' https://excelmacromastery.com/ Option Compare Text Sub Comp2() ' Strings match - uses vbCompareText as Compare argument Debug.Print StrComp("ABC", "abc") Debug.Print StrComp("DEF", "def") End Sub
2. Option Compare Binary: Makes vbBinaryCompare the default Compare argument
' https://excelmacromastery.com/ Option Compare Binary Sub Comp2() ' Strings do not match - uses vbCompareBinary as Compare argument Debug.Print StrComp("ABC", "abc") Debug.Print StrComp("DEF", "def") End Sub
If Option Compare is not used then the default is Option Compare Binary.
Now that you understand these two important points about string we can go ahead and look at the string functions individually.
Appending Strings
You can append strings using the & operator. The following code shows some examples of using it
' https://excelmacromastery.com/ Sub Append() Debug.Print "ABC" & "DEF" Debug.Print "Jane" & " " & "Smith" Debug.Print "Long " & 22 Debug.Print "Double " & 14.99 Debug.Print "Date " & #12/12/2015# End Sub
You can see in the example that different types such as dates and number are automatically converted to strings. You may see the + operator being used to append strings. The difference is that this operator will only work with string types. If you try to use it with other type you will get an error.
' This will give the error message: "Type Mismatch" Debug.Print "Long " + 22
If you want to do more complex appending of strings then you may wish to use the Format function described below.
Extracting Part of a String
The functions discussed in this section are useful when dealing with basic extracting from a string. For anything more complicated you might want to check out my post on How to Easily Extract From Any String Without Using VBA InStr.
Function | Parameters | Description | Example |
---|---|---|---|
Left | string, length | Return chars from left side | Left("John Smith",4) |
Right | string, length | Return chars from right side | Right("John Smith",5) |
Mid | string, start, length | Return chars from middle | Mid("John Smith",3,2) |
The Left, Right, and Mid functions are used to extract parts of a string. They are very simple functions to use. Left reads characters from the left, Right from the right and Mid from a starting point that you specify.
' https://excelmacromastery.com/ Sub UseLeftRightMid() Dim sCustomer As String sCustomer = "John Thomas Smith" Debug.Print Left(sCustomer, 4) ' Prints: John Debug.Print Right(sCustomer, 5) ' Prints: Smith Debug.Print Left(sCustomer, 11) ' Prints: John Thomas Debug.Print Right(sCustomer, 12) ' Prints: Thomas Smith Debug.Print Mid(sCustomer, 1, 4) ' Prints: John Debug.Print Mid(sCustomer, 6, 6) ' Prints: Thomas Debug.Print Mid(sCustomer, 13, 5) ' Prints: Smith End Sub
As mentioned in the previous section, VBA string functions do not change the original string. Instead, they return the result as a new string.
In the next example you can see that the string Fullname was not changed after using the Left function
' https://excelmacromastery.com/ Sub UsingLeftExample() Dim Fullname As String Fullname = "John Smith" Debug.Print "Firstname is: "; Left(Fullname, 4) ' Original string has not changed Debug.Print "Fullname is: "; Fullname End Sub
If you want to change the original string you simply assign it to the return value of the function
' https://excelmacromastery.com/ Sub ChangingString() Dim name As String name = "John Smith" ' Assign return string to the name variable name = Left(name, 4) Debug.Print "Name is: "; name End Sub
Searching Within a String
Function | Params | Description | Example |
---|---|---|---|
InStr | String1, String2 | Finds position of string | InStr("John Smith","h") |
InStrRev | StringCheck, StringMatch | Finds position of string from end | InStrRev("John Smith","h") |
InStr and InStrRev are VBA functions used to search through strings for a substring. If the search string is found then the position(from the start of the check string) of the search string is returned. If the search string is not found then zero is returned. If either string is null then null is returned.
InStr Description of Parameters
InStr() Start[Optional], String1, String2, Compare[Optional]
- Start As Long[Optional – Default is 1]: This is a number that specifies the starting search position from the left
- String1 As String: The string to search
- String2 As String: The string to search for
- Compare As vbCompareMethod : See the section on Compare above for more details
InStr Use and Examples
InStr returns the first position in a string where a given substring is found. The following shows some examples of using it
' https://excelmacromastery.com/ Sub FindSubString() Dim name As String name = "John Smith" ' Returns 3 - position of first h Debug.Print InStr(name, "h") ' Returns 10 - position of first h starting from position 4 Debug.Print InStr(4, name, "h") ' Returns 8 Debug.Print InStr(name, "it") ' Returns 6 Debug.Print InStr(name, "Smith") ' Returns 0 - string "SSS" not found Debug.Print InStr(name, "SSS") End Sub
InStrRev Description of Parameters
InStrRev() StringCheck, StringMatch, Start[Optional], Compare[Optional]
- StringCheck As String: The string to search
- StringMatch: The string to search for
- Start As Long[Optional – Default is -1]: This is a number that specifies the starting search position from the right
- Compare As vbCompareMethod: See the section on Compare above for more details
InStrRev Use and Examples
The InStrRev function is the same as InStr except that it searches from the end of the string. It’s important to note that the position returned is the position from the start. Therefore if there is only one instance of the search item then both InStr() and InStrRev() will return the same value.
The following code show some examples of using InStrRev
' https://excelmacromastery.com/ Sub UsingInstrRev() Dim name As String name = "John Smith" ' Both Return 1 - position of the only J Debug.Print InStr(name, "J") Debug.Print InStrRev(name, "J") ' Returns 10 - second h Debug.Print InStrRev(name, "h") ' Returns 3 - first h as searches from position 9 Debug.Print InStrRev(name, "h", 9) ' Returns 1 Debug.Print InStrRev(name, "John") End Sub
The InStr and InStrRev functions are useful when dealing with basic string searches. However, if you are going to use them for extracting text from a string they can make things complicated. I have written about a much better way to do this in my post How to Easily Extract From Any String Without Using VBA InStr.
Removing Blanks
Function | Params | Description | Example |
---|---|---|---|
LTrim | string | Removes spaces from left | LTrim(" John ") |
RTrim | string | Removes spaces from right | RTrim(" John ") |
Trim | string | Removes Spaces from left and right | Trim(" John ") |
The Trim functions are simple functions that remove spaces from either the start or end of a string.
Trim Functions Use and Examples
- LTrim removes spaces from the left of a string
- RTrim removes spaces from the right of a string
- Trim removes spaces from the left and right of a string
' https://excelmacromastery.com/ Sub TrimStr() Dim name As String name = " John Smith " ' Prints "John Smith " Debug.Print LTrim(name) ' Prints " John Smith" Debug.Print RTrim(name) ' Prints "John Smith" Debug.Print Trim(name) End Sub
Length of a String
Function | Params | Description | Example |
---|---|---|---|
Len | string | Returns length of string | Len ("John Smith") |
Len is a simple function when used with a string. It simply returns the number of characters the string contains. If used with a numeric type such as long it will return the number of bytes.
' https://excelmacromastery.com/ Sub GetLen() Dim name As String name = "John Smith" ' Prints 10 Debug.Print Len("John Smith") ' Prints 3 Debug.Print Len("ABC") ' Prints 4 as Long is 4 bytes in size Dim total As Long Debug.Print Len(total) End Sub
Reversing a String
Function | Params | Description | Example |
---|---|---|---|
StrReverse | string | Reverses a string | StrReverse ("John Smith") |
StrReverse is another easy-to-use function. It simply returns the given string with the characters reversed.
' https://excelmacromastery.com/ Sub RevStr() Dim s As String s = "Jane Smith" ' Prints: htimS enaJ Debug.Print StrReverse(s) End Sub
Comparing Strings
Function | Params | Description | Example |
---|---|---|---|
StrComp | string1, string2 | Compares 2 strings | StrComp ("John", "John") |
The function StrComp is used to compare two strings. The following subsections describe how it is used.
Description of Parameters
StrComp() Â String1, String2, Compare[Optional]
- String1 As String: The first string to compare
- String2 As String: The second string to compare
- Compare As vbCompareMethod : See the section on Compare above for more details
StrComp Return Values
Return Value | Description |
---|---|
0 | Strings match |
-1 | string1 less than string2 |
1 | string1 greater than string2 |
Null | if either string is null |
Use and Examples
The following are some examples of using the StrComp function
' https://excelmacromastery.com/ Sub UsingStrComp() ' Returns 0 Debug.Print StrComp("ABC", "ABC", vbTextCompare) ' Returns 1 Debug.Print StrComp("ABCD", "ABC", vbTextCompare) ' Returns -1 Debug.Print StrComp("ABC", "ABCD", vbTextCompare) ' Returns Null Debug.Print StrComp(Null, "ABCD", vbTextCompare) End Sub
Compare Strings using Operators
You can also use the equals sign to compare strings. The difference between the equals comparison and the StrComp function are:
- The equals sign returns only true or false.
- You cannot specify a Compare parameter using the equal sign – it uses the “Option Compare” setting.
The following shows some examples of using equals to compare strings
' https://excelmacromastery.com/ Option Compare Text Sub CompareUsingEquals() ' Returns true Debug.Print "ABC" = "ABC" ' Returns true because "Compare Text" is set above Debug.Print "ABC" = "abc" ' Returns false Debug.Print "ABCD" = "ABC" ' Returns false Debug.Print "ABC" = "ABCD" ' Returns null Debug.Print Null = "ABCD" End Sub
The Operator “<>” means “does not equal”. It is essentially the opposite of using the equals sign as the following code shows
' https://excelmacromastery.com/ Option Compare Text Sub CompareWithNotEqual() ' Returns false Debug.Print "ABC" <> "ABC" ' Returns false because "Compare Text" is set above Debug.Print "ABC" <> "abc" ' Returns true Debug.Print "ABCD" <> "ABC" ' Returns true Debug.Print "ABC" <> "ABCD" ' Returns null Debug.Print Null <> "ABCD" End Sub
Comparing Strings using Pattern Matching
Operator | Params | Description | Example |
---|---|---|---|
Like | string, string pattern | checks if string has the given pattern | "abX" Like "??X" "54abc5" Like "*abc#" |
Token | Meaning |
---|---|
? | Any single char |
# | Any single digit(0-9) |
* | zero or more characters |
[charlist] | Any char in the list |
[!charlist] | Any char not in the char list |
Pattern matching is used to determine if a string has a particular pattern of characters. For example, you may want to check that a customer number has 3 digits followed by 3 alphabetic characters or a string has the letters XX followed by any number of characters.
If the string matches the pattern then the return value is true, otherwise it is false.
Pattern matching is similar to the VBA Format function in that there are almost infinite ways to use it. In this section I am going to give some examples that will explain how it works. This should cover the most common uses. If you need more information about pattern matching you can refer to the MSDN Page for the Like operator.
Lets have a look at a basic example using the tokens. Take the following pattern string
[abc][!def]?#X*
Let’s look at how this string works
[abc] a character that is either a,b or c
[!def] a character that is not d,e or f
? any character
# any digit
X the character X
* followed by zero or more characters
Therefore the following string is valid
apY6X
a is one of abc
p is not one of the characters d, e or f
Y is any character
6 is a digit
X is the letter X
The following code examples show the results of various strings with this pattern
' https://excelmacromastery.com/ Sub Patterns() ' True Debug.Print 1; "apY6X" Like "[abc][!def]?#X*" ' True - any combination of chars after x is valid Debug.Print 2; "apY6Xsf34FAD" Like "[abc][!def]?#X*" ' False - char d not in [abc] Debug.Print 3; "dpY6X" Like "[abc][!def]?#X*" ' False - 2nd char e is in [def] Debug.Print 4; "aeY6X" Like "[abc][!def]?#X*" ' False - A at position 4 is not a digit Debug.Print 5; "apYAX" Like "[abc][!def]?#X*" ' False - char at position 5 must be X Debug.Print 6; "apY6Z" Like "[abc][!def]?#X*" End Sub
Real-World Example of Pattern Matching
To see a real-world example of using pattern matching check out Example 3: Check if a filename is valid.
Important Note on VBA Pattern Matching
The Like operator uses either Binary or Text comparison based on the Option Compare setting. Please see the section on Compare above for more details.
Replace Part of a String
Function | Params | Description | Example |
---|---|---|---|
Replace | string, find, replace, start, count, compare | Replaces a substring with a substring | Replace ("Jon","n","hn") |
Replace is used to replace a substring in a string by another substring. It replaces all instances of the substring that are found by default.
Replace Description of Parameters
Replace() Â Expression, Find, Replace, Start[Optional], Count[Optional], Compare[Optional]
- Expression As String: The string to replace chars in
- Find As String: The substring to replace in the Expression string
- Replace As String: The string to replace the Find substring with
- Start As Long[Optional – Default is 1]: The start position in the string
- Count As Long[Optional – Default is -1]: The number of substitutions to make. The default -1 means all.
- Compare As vbCompareMethod : See the section on Compare above for more details
Use and Examples
The following code shows some examples of using the Replace function
' https://excelmacromastery.com/ Sub ReplaceExamples() ' Replaces all the question marks with(?) with semi colons(;) Debug.Print Replace("A?B?C?D?E", "?", ";") ' Replace Smith with Jones Debug.Print Replace("Peter Smith,Ann Smith", "Smith", "Jones") ' Replace AX with AB Debug.Print Replace("ACD AXC BAX", "AX", "AB") End Sub
Output
A;B;C;D;E
Peter Jones,Sophia Jones
ACD ABC BAB
In the following examples we use the Count optional parameter. Count determines the number of substitutions to make. So for example, setting Count equal to one means that only the first occurrence will be replaced.
' https://excelmacromastery.com/ Sub ReplaceCount() ' Replaces first question mark only Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=1) ' Replaces first three question marks Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=3) End Sub
Output
A;B?C?D?E
A;B;C;D?E
The Start optional parameter allow you to return part of a string. The position you specify using Start is where it starts returning the string from. It will not return any part of the string before this position whether a replace was made or not.
' https://excelmacromastery.com/ Sub ReplacePartial() ' Use original string from position 4 Debug.Print Replace("A?B?C?D?E", "?", ";", Start:=4) ' Use original string from position 8 Debug.Print Replace("AA?B?C?D?E", "?", ";", Start:=8) ' No item replaced but still only returns last 2 characters Debug.Print Replace("ABCD", "X", "Y", Start:=3) End Sub
Output
;C;D;E
;E
CD
Sometimes you may only want to replace only upper or lower case letters. You can use the Compare parameter to do this. This is used in a lot of string functions. Â For more information on this check out the Compare section above.
' https://excelmacromastery.com/ Sub ReplaceCase() ' Replace capital A's only Debug.Print Replace("AaAa", "A", "X", Compare:=vbBinaryCompare) ' Replace All A's Debug.Print Replace("AaAa", "A", "X", Compare:=vbTextCompare) End Sub
Output
XaXa
XXXX
Multiple Replaces
If you want to replace multiple values in a string you can nest the calls. In the following code we want to replace X and Y with A and B respectively.
' https://excelmacromastery.com/ Sub ReplaceMulti() Dim newString As String ' Replace A with X newString = Replace("ABCD ABDN", "A", "X") ' Now replace B with Y in new string newString = Replace(newString, "B", "Y") Debug.Print newString End Sub
In the next example we will change the above code to perform the same task. We will use the return value of the first replace as the argument for the second replace.
' https://excelmacromastery.com/ Sub ReplaceMultiNested() Dim newString As String ' Replace A with X and B with Y newString = Replace(Replace("ABCD ABDN", "A", "X"), "B", "Y") Debug.Print newString End Sub
The result of both of these Subs is
XYCD XYDN
Convert Types to String(Basic)
This section is about converting numbers to a string. A very important point here is that most the time VBA will automatically convert to a string for you. Let’s look at some examples
' https://excelmacromastery.com/ Sub AutoConverts() Dim s As String ' Automatically converts number to string s = 12.99 Debug.Print s ' Automatically converts multiple numbers to string s = "ABC" & 6 & 12.99 Debug.Print s ' Automatically converts double variable to string Dim d As Double, l As Long d = 19.99 l = 55 s = "Values are " & d & " " & l Debug.Print s End Sub
When you run the above code you can see that the number were automatically converted to strings. So when you assign a value to a string VBA will look after the conversion for you most of the time. There are conversion functions in VBA and in the following sub sections we will look at the reasons for using them.
Explicit Conversion
Function | Params | Description | Example |
---|---|---|---|
CStr | expression | Converts a number variable to a string | CStr ("45.78") |
Str | number | Converts a number variable to a string | Str ("45.78") |
In certain cases you may want to convert an item to a string without have to place it in a string variable first. In this case you can use the Str or CStr functions. Both take an  expression as a function and this can be any type such as long, double, data or boolean.
Let’s look at a simple example. Imagine you are reading a list of values from different types of cells to a collection. You can use the Str/CStr functions to ensure they are all stored as strings. The following code shows an example of this
' https://excelmacromastery.com/ Sub UseStr() Dim coll As New Collection Dim c As Range ' Read cell values to collection For Each c In Range("A1:A10") ' Use Str to convert cell value to a string coll.Add Str(c) Next ' Print out the collection values and type Dim i As Variant For Each i In coll Debug.Print i, TypeName(i) Next End Sub
In the above example we use Str to convert the value of the cell to a string. The alternative to this would be to assign the value to a string and then assigning the string to the collection. So you can see that using Str here is much more efficient.
Multi Region
The difference between the Str and CStr functions is that CStr converts based on the region. If your macros will be used in multiple regions then you will need to use CStr for your string conversions.
It is good to practise to use CStr when reading values from cells. If your code ends up being used in another region then you will not have to make any changes to make it work correctly.
Convert String to Number-Â CLng, CDbl, Val etc.
Function | Returns | Example |
---|---|---|
CBool | Boolean | CBool("True"), CBool("0") |
CCur | Currency | CCur("245.567") |
CDate | Date | CDate("1/1/2017") |
CDbl | Double | CCur("245.567") |
CDec | Decimal | CDec("245.567") |
CInt | Integer | CInt("45") |
CLng | Long Integer | CLng("45.78") |
CVar | Variant | CVar("") |
The above functions are used to convert strings to various types. If you are assigning to a variable of this type then VBA will do the conversion automatically.
' https://excelmacromastery.com/ Sub StrToNumeric() Dim l As Long, d As Double, c As Currency Dim s As String s = "45.923239" l = s d = s c = s Debug.Print "Long is "; l Debug.Print "Double is "; d Debug.Print "Currency is "; c End Sub
Using the conversion types gives more flexibility. It means you can determine the type at runtime. In the following code we set the type based on the sType argument passed to the PrintValue function. As this type can be read from an external source such as a cell, we can set the type at runtime. If we declare a variable as Long then it will always be long when the code runs.
' https://excelmacromastery.com/ Sub Test() ' Prints 46 PrintValue "45.56", "Long" ' Print 45.56 PrintValue "45.56", "" End Sub Sub PrintValue(ByVal s As String, ByVal sType As String) Dim value ' Set the data type based on a type string If sType = "Long" Then value = CLng(s) Else value = CDbl(s) End If Debug.Print "Type is "; TypeName(value); value End Sub
If a string is not a valid number(i.e. contains symbols other numeric) then you get a “Type Mismatch” error.
' https://excelmacromastery.com/ Sub InvalidNumber() Dim l As Long ' Will give type mismatch error l = CLng("45A") End Sub
The Val Function
The value function convert numeric parts of a string to the correct number type.
The Val function converts the first numbers it meets. Once it meets letters in a string it stops. If there are only letters then it returns zero as the value. The following code shows some examples of using Val
' https://excelmacromastery.com/ Sub UseVal() ' Prints 45 Debug.Print Val("45 New Street") ' Prints 45 Debug.Print Val(" 45 New Street") ' Prints 0 Debug.Print Val("New Street 45") ' Prints 12 Debug.Print Val("12 f 34") End Sub
The Val function has two disadvantages
1. Not Multi-Region – Val does not recognise international versions of numbers such as using commas instead of decimals. Therefore you should use the above conversion functions when you application will be used in multiple regions.
2. Converts invalid strings to zero – This may be okay in some instances but in most cases it is better if an invalid string raises an error. The application is then aware there is a problem and can act accordingly. The conversion functions such as CLng will raise an error if the string contains non-numeric characters.
Generate a String of items – String Function
Function | Params | Description | Example |
---|---|---|---|
String | number, character | Converts a number variable to a string | String (5,"*") |
The String function is used to generate a string of repeated characters. The first argument is the number of times to repeat it, the second argument is the character.
' https://excelmacromastery.com/ Sub GenString() ' Prints: AAAAA Debug.Print String(5, "A") ' Prints: >>>>> Debug.Print String(5, 62) ' Prints: (((ABC))) Debug.Print String(3, "(") & "ABC" & String(3, ")") End Sub
Convert Case/Unicode – StrConv, UCase, LCase
Function | Params | Description | Example |
---|---|---|---|
StrConv | string, conversion, LCID | Converts a String | StrConv("abc",vbUpperCase) |
If you want to convert the case of a string to upper or lower you can use the UCase and LCase functions for upper and lower respectively. You can also use the StrConv function with the vbUpperCase or vbLowerCase argument. The following code shows example of using these three functions
' https://excelmacromastery.com/ Sub ConvCase() Dim s As String s = "Mary had a little lamb" ' Upper Debug.Print UCase(s) Debug.Print StrConv(s, vbUpperCase) ' Lower Debug.Print LCase(s) Debug.Print StrConv(s, vbLowerCase) ' Sets the first letter of each word to upper case Debug.Print StrConv(s, vbProperCase) End Sub
Output
MARY HAD A LITTLE LAMB
MARY HAD A LITTLE LAMB
mary had a little lamb
mary had a little lamb
Mary Had A Little Lamb
Other Conversions
As well as case the StrConv can perform other conversions based on the Conversion parameter. The following table shows a list of the different parameter values and what they do. For more information on StrConv check out the MSDN Page.
Constant | Value | Converts |
---|---|---|
vbUpperCase | 1 | to upper case |
vbLowerCase | 2 | to lower case |
vbProperCase | 3 | first letter of each word to uppercase |
vbWide* | 4 | from Narrow to Wide |
vbNarrow* | 8 | from Wide to Narrow |
vbKatakana** | 16 | from Hiragana to Katakana |
vbHiragana | 32 | from Katakana to Hiragana |
vbUnicode | 64 | to unicode |
vbFromUnicode | 128 | from unicode |
Using Strings With Arrays
Function | Params | Description | Example |
---|---|---|---|
Split | expression, delimiter, limit, compare | Parses a delimited string to an array | arr = Split("A;B;C",";") |
Join | source array, delimiter | Converts a one dimensional array to a string | s = Join(Arr, ";") |
String to Array using Split
You can easily parse a delimited string into an array. You simply use the Split function with the delimiter as parameter. The following code shows an example of using the Split function.
' https://excelmacromastery.com/ Sub StrToArr() Dim arr() As String ' Parse string to array arr = Split("John,Jane,Paul,Sophie", ",") Dim name As Variant For Each name In arr Debug.Print name Next End Sub
Output
John
Jane
Paul
Sophie
You can find a complete guide to the split function here.
Array to String using Join
If you want to build a string from an array you can do so easily using the Join function. This is essentially a reverse of the Split function. The following code provides an example of using Join
' https://excelmacromastery.com/ Sub ArrToStr() Dim Arr(0 To 3) As String Arr(0) = "John" Arr(1) = "Jane" Arr(2) = "Paul" Arr(3) = "Sophie" ' Build string from array Dim sNames As String sNames = Join(Arr, ",") Debug.Print sNames End Sub
Output
John,Jane,Paul,Sophie
Formatting a String
Function | Params | Description | Example |
---|---|---|---|
Format | expression, format, firstdayofweek, firstweekofyear | Formats a string | Format(0.5, "0.00%") |
The Format function is used to format a string based on given instructions. It is mostly used to place a date or number in certain format. The examples below show the most common ways you would format a date.
' https://excelmacromastery.com/ Sub FormatDate() Dim s As String s = "31/12/2015 10:15:45" ' Prints: 31 12 15 Debug.Print Format(s, "DD MM YY") ' Prints: Thu 31 Dec 2015 Debug.Print Format(s, "DDD DD MMM YYYY") ' Prints: Thursday 31 December 2015 Debug.Print Format(s, "DDDD DD MMMM YYYY") ' Prints: 10:15 Debug.Print Format(s, "HH:MM") ' Prints: 10:15:45 AM Debug.Print Format(s, "HH:MM:SS AM/PM") End Sub
The following examples are some common ways of formatting numbers
' https://excelmacromastery.com/ Sub FormatNumbers() ' Prints: 50.00% Debug.Print Format(0.5, "0.00%") ' Prints: 023.45 Debug.Print Format(23.45, "00#.00") ' Prints: 23,000 Debug.Print Format(23000, "##,000") ' Prints: 023,000 Debug.Print Format(23000, "0##,000") ' Prints: $23.99 Debug.Print Format(23.99, "$#0.00") End Sub
The Format function is quite a large topic and could use up a full post on it’s own. If you want more information then the MSDN Format Page provides a lot of information.
Helpful Tip for Using Format
A quick way to figure out the formatting to use is by using the cell formatting on an Excel worksheet. For example add a number to a cell. Then right click and format the cell the way you require. When you are happy with the format select Custom from the category listbox on the left.  When you select this you can see the format string in the type textbox(see image below). This is the string format you can use in VBA.
Conclusion
In almost any type of programming, you will spend a great deal of time manipulating strings. This post covers the many different ways you use strings in VBA.
To get the most from use the table at the top to find the type of function you wish to use. Clicking on the left column of this function will bring you to that section.
If you are new to strings in VBA, then I suggest you check out the Read this First section before using any of the functions.
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Awesome post. Great to have all this information in one place. I wasn’t aware that VBA had pattern matching. I guess I learned something new today:-)
Thanks John, Really glad to hear you like it. I spent a lot of time getting it right. Feel free the check out my other posts. The most popular one is The Complete Guide to Using Arrays in Excel VBA
Keep up the good work Paul! Your posts are incredibly simple, easy to follow and yet contains lots of useful stuff!! Hats off to you!Please keep posting!!!
Hi Narendran,
Thanks very much for you comment. Glad you are finding the site so useful.
Paul
super
excellent
I am running out of words to appreciate your efforts/pain taken by you.
you are a very very good teacher
Thanks Srinivasan
Awesome. I gained tremmendous knowledge. This weekend, I am going to go through each and every page of your blog.
Thanks V.A. Glad you found it useful.
You truly do posses in-depth knowledge of VBA. I can only say you are great at it!
Please what are the commercial benefits of knowing VBA or how can it be commercialized?
Thank you
There is a good answer to this on Quora
hai
i want to know how to adjust a size of a array at runtime as per the user’s
input.please rply me.
Hi Sri,
What you are looking for is a dynamic array. If you check out this post here it explains how to use them.
Regards
Paul
Marvelous blog…. hats off to you.. Keep posting…
thank you for your valuable time for writing valuable information and sharing the knowledge you earned through untiring efforts..
This is awesome. I first encountered Paul on Udemy and wish I had come across him many years earlier. I have learned more about VBA than I could have imagined. After completing the Udemy course I purchase Modules 1 and 2 of His VBA Handbook and completed the exercises. I since acquired his book “Excel Macro Mastery – How You Can Write VBA Like a Professional in 15 Simple Steps” and am working through the chapters. This Blog is perhaps the most valuable VBA resource that is available and I cannot thank Paul enough for making it freely available. Thanks heaps Paul
Thanks John, High praise indeed.
Such an amazing website ! Thank you so much for your time & efforts for knowledge sharing. There is plenty of new things for me to learn here … New page added to my favorites. Will definitely be reading/learning a lot in your page
Keep up the good
Thanks Naser.
Very nice and useful , eased so many things for me thanks Paul
Hi, Paul
A very good blog. All information about string manipulation in VBA together.
I think there is a litlle mistake, a slip of the pen in text:
Option Compare Text
Sub CompareWithNotEqual()
‘ Returns false
Debug.Print “ABC” “ABC”
‘ Returns true because “Compare Text” is set above
Debug.Print “ABC” “abc”
In compare Text-mode “ABC”=”abc”, so “ABC””abc” is false.
After correcting the text in the blog, you can remove my remark about the mistake.
Do you have an email address for reporting mistakes?
Thanks Frank, I’ve updated the code.
Hi Paul,
great and very clear post as always! At the moment I’m trying to approach Regex as a bit more advanced way for string manipulation. Have you ever considered a post about it? I found lots of websites, but they are rather confusing compared to your way of explanation.
Thanks for the compliment Irene.
I use this webpage for testing my regular expressions before I put them in code.
It saves a lot of time and you can figure out if what you are trying will work.
I haven’t any plan to write a post on this in the near future.
There is a useful explanation here with a lot of useful links.
Hi, Thank you for your teaching. Please, in case it’s possible; can you show Us a little bit about the kind of code that generate a second word after click the first one, the example about this is the IDE on VBA, as soon as you write Sub —-something () the IDE automatically generate the () and the End Sub words, PLEASE can you Teach this kind of code. Thank you.
“The Original String is not Changed”
“An important point to remember is that the VBA string functions do not change the original string. ”
That statement is false. Explore this example:
Mid(,,<length.) =
Some VBA string functions are bidirectional. WHG
My hosed my code example, recast here:
Mid([target string],[position],[length]) = [source string]
WHG
Superb.. great work.. very good explanation.
Thank you
thank you excelmacromasteryfor giving me wonderful information
Thank you so much for your website and contents of VBA. It helps me a lot.
Hello,
I have problem like this.
asp = Application.Index(a_sn, [row(rowstr)], Application.Transpose([colstr]))
rowstr = “1:” & lrow -> lrow is last row = “1:8”
colstr = “1:” & lcol ->lcol is last column =”1:10″
asp gives error when i use rowstr and colstr.
When i put just hardwritten numbers with colon it works…
what convert i need to do
asp = Application.Index(a_sn, [row(1:8)], Application.Transpose([1:10]))
This works….
How to make possible use this 1:8 and 1:10 and convert to what?
Br,
Daniel