Tags

, , , , , , ,

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) :

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:

Excel VBA, Strings, Adding


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:

Excel VBA, Strings, MID


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:

Excel VBA, Strings, LEFT


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:

Excel VBA, Strings, RIGHT


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:

Excel VBA, Strings, GetCharacter


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:

Excel VBA, Strings, Length


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:

Excel VBA, String, Conversion


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:

Excel, VBA, String, Trim


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:

Excel VBA, String, SPLIT


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:

11
39
64
85

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:

Excel VBA, String, FIND

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

See Also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Advertisements