VBA Excel String Processing and Manipulation

Jun 10, 2015 by azurous in Adding/Concatenating Strings

When you are working with strings, string processing and manipulation is an important tool. Whether you want to search for a certain expression in a string, split a string, add multiple strings … you will have to be familiar with the concepts of string processing. In this article I will explain the most important functions when working with string variables:

You can download the codes and files related to this article here.

Jump To:

Strings, Adding (Concatenating)
Strings.Mid()
Strings.Left()
Strings.Right()
Get Character From String at Specific Index
String Length, Strings.Len()
String Conversions
Converting Variables to String
Strings, Removing Trailing and Leading Spaces, Strings.Trim(), Strings.LTrim(), Strings.RTrim()
Strings, Splitting Based on Delimiter Strings.Split()
Strings, Searching Strings.InStr()

–

Contents

Strings, Adding (Concatenating) :

The example below adds two string and prints the results in cells column A. Four different method for adding (Concatenating) the two strings have been used which all yield the same result:

Sub Example1() Dim str1 As String Dim str2 As String str1 = "This is String1" str2 = "This is String2" 'method1 Cells(2, 1) = str1 + str2 'method2 Cells(3, 1) = str1 & str2 'method 3 Cells(4, 1) = "This is String1" + "This is String2" 'method 4 Cells(5, 1) = "This is String1" & "This is String2" End Sub

Result:

–

Strings.Mid() (Substring):

Returns a substring from the middle of a string. The example below returns the substring that starts from the index 4 of the string “This is String1” with a length of 3. The final result is “s i“”

Sub Example2() Dim InputString As String Dim StartIndex As Integer Dim Length As Integer 'The string to get the substring from InputString = "This is String1" 'the start index, note that strings start with the index "1" StartIndex = 4 'The number of character to take Length = 3 Cells(1, 1) = Strings.Mid(InputString, StartIndex, Length) End Sub

Result:

–

Strings.Left() (Substring):

Strings.Left() returns the left portion of an input string based on the length specified. The example below returns the 6 left characters of the string “This is String1”. The result is “This i“. Note that the results of the code below would be the same had we used Strings.Mid() with a start index of 1:

Sub Example3() Dim InputString As String Dim Length As Integer InputString = "This is String1" Length = 6 Cells(1, 1) = Strings.Left(InputString, Length) 'This will generate the same result Cells(1, 2) = Strings.Mid(InputString, 1, Length) End Sub

Result:

–

Strings.Right() (Substring):

Strings.Right() returns the right portion of an input string based on the length specified. The example below returns the 7 right characters of the string “This is String1”. The result is “String1“. The equivellant code using String.Mid() has also been presented in the example:

Sub Example4() Dim InputString As String Dim Length As Integer InputString = "This is String1" Length = 7 Cells(1, 1) = Strings.Right(InputString, Length) 'This will generate the same results Cells(1, 2) = Strings.Mid(InputString, _ Strings.Len(InputString) - Length + 1, Length) End Sub

Result:

–

Get Character From String at Specific Index:

In order to get a character at a specific index in a string you would have to use the Strings.Mid() function. The function below receives a string and an index as input and returns the corresponding character at the index provided:

Function GetCharacter(ByVal strInput As String, _ ByVal Index As Integer) As String GetCharacter = Strings.Mid(strInput, Index, 1) End Function

Sub Example5() Cells(1, 1) = GetCharacter("Sample Input", 2) End Sub

Result:

–

String Length, Strings.Len():

The Strings.Len() function returns the length of the input string and prints the number in cell A1:

Sub Example6()
Cells(1, 1) = Strings.Len(“This is String1”)
End Sub

Result:

–

String Conversions (Str)

using the Str() function you can convert any data type to a string. The code below converts the integer, double, long and date data types to string, and prints the results in column A:

Sub Example7() Dim strTemp As String Dim intTemp As Integer Dim dblTemp As Double Dim dateTemp As Date Dim longTemp As Long intTemp = 3 dblTemp = 4.2534 longTemp = 54686484 dateTemp = DateSerial(1999, 3, 5) 'converts integer to string strTemp = Str(intTemp) Cells(1, 1) = strTemp 'converts double to string strTemp = Str(dblTemp) Cells(2, 1) = strTemp 'converts long to string strTemp = Str(longTemp) Cells(3, 1) = strTemp 'converts date to string strTemp = Str(dateTemp) Cells(4, 1) = strTemp End Sub

Result:

–

Strings, Removing Trailing and Leading Spaces, Strings.Trim(), Strings.LTrim(), Strings.RTrim():

There will often be times when you have to remove spaces from either the beginning, end or both sides of the string. This can be accomplished using the Strings.Trim(), Strings.LTrim() and Strings.RTrim() functions:

Sub Example8() Dim strInput As String strInput = " sometext " 'removes spaces from start and end of string Cells(1, 1) = Strings.Trim(strInput) 'removes spaces from start of string Cells(2, 1) = Strings.LTrim(strInput) 'removes spaces from end od string Cells(3, 1) = Strings.RTrim(strInput) End Sub

Result:

–

Strings, Splitting Based on Delimiter Strings.Split():

Using the Strings.Split() function you can convert a string into an array. The string is split based on the delimiter provided. For example take into account the string “Data1, Data2, Data3, Data4, Data5”. We want to put each of the Datai strings in a separate variable. we would use the “, ” string as a delimiter. The result would be an array with the following date {“Data1”, “Data2”, “Data3”, “Data4”, “Data5”}. Note that the resulting string array is zero based:

Sub Example9() Dim strInput As String Dim arrStrings() As String Dim i As Integer strInput = "Data1, Data2, Data3, Data4, Data5" arrStrings = Strings.Split(strInput, ", ") 'note that the resulting array is zero based For i = 0 To UBound(arrStrings) Cells(i + 1, 1) = arrStrings(i) Next i End Sub

Result:

–

Strings, Searching Strings.InStr():

The function Strings.InStr() receives as input a string to search, a target string to find, and an index to start searching. If it finds a match it will return the index of the first match. If it fails to find a match it will return the value “0”. The code below searches the string “afkjh82rf FindThis 45643*Uhu3jbrwebfv FindThisfeqwpgnb 98t8934FindThisrh4i2p3t48798FindThis” for the value “FindThis”. It records all the indices at which the target string was found in a collection. In the end it prints the results in column I. The results are:

Sub example10() 'The string to search for the target string Dim strTemp As String 'the indices at which the target string was found Dim colIndex As Collection 'a boolean variable that tells the program whether to stop or go on Dim flag As Boolean 'the index at which the target variable was found Dim intIndex As Integer 'a counter used for printing the results Dim i As Integer strTemp = "afkjh82rf FindThis 45643*Uhu3jbrwebfv FindThisfeqwpgnb 98t8934FindThisrh4i2p3t48798FindThis" Set colIndex = New Collection flag = True 'Searches for the string "FindThis" in the string strTemp, starting from the index "1' 'note that strings start at the index "1" intIndex = Strings.InStr(1, strTemp, "FindThis") 'checks to see if the target string was found. The value zero would mean that the target string was not found While flag = True If intIndex <> 0 Then 'add the index to the collection of indices colIndex.Add (intIndex) 'the +1 is so that it would not return the same index again intIndex = Strings.InStr(intIndex + 1, strTemp, "FindThis") Else 'if the target string was not found exit the loop flag = False End If Wend For i = 1 To colIndex.Count Cells(i + 1, 1) = colIndex.Item(i) Next i End Sub

Result:

You can download the codes and files related to this from the link below:

String Processing.xlsm