VBA String Manipulation Functions & Operators

VBA String Operators

String data is used to hold data that is made up of numbers, characters, and symbols. “Jul-2015” is an example of a string data. It is made up of

• Characters (Jul)
• Symbol (-)
• Numbers (2015)

String operators are used to manipulate string data. For example, you can concatenate the value of July-2015 from the first 3 letters of the month and the year like “Jul-2015”.

The following table shows the concatenation VBA string operator.

Example Source Code

MsgBox "John " & "Doe", vbOKOnly, "Concatenate Operator"

Executing the above code produces the following result

The ampersand is not the only symbol that appears to join text, and the difference between the two matters once numbers are involved.

Difference Between & and + for String Concatenation

VBA accepts both the ampersand and the plus sign between two text values, so beginners often treat them as interchangeable. They behave differently as soon as one operand is a number or an empty value, and only one of them is safe.

💡 Tip: Always use the ampersand for concatenation and reserve the plus sign for arithmetic operations. Put a space before and after the ampersand, because writing Total& is read by VBA as a Long type declaration character.

VBA String Manipulation Functions

VBA String Manipulation is an important element in Excel VBA for Macro programming and string operations. Excel provides various types of VBA String Manipulation functions like:

• Left: is used to extract characters from the left side of the string
• Right: is used to extract characters from the right side of the string
• Mid: is used to extract a substring from the middle of a string
• Len: is used to find the length of a string
• InStr: is used to find the position of a substring in a string
• Trim: is used to remove spaces from both ends of a string
• Replace: is used to swap every occurrence of one substring for another
• UCase / LCase: are used to convert a string to upper case or lower case

The table below shows the syntax of each function with a worked example, so you can copy the pattern straight into a macro.

The macro below combines several of these functions to rebuild a date label from its parts.

Sub StringFunctionsDemo()
    Dim Source As String
    Dim MonthPart As String
    Dim YearPart As String

    Source = "July-2015"

    ' Take the first three characters
    MonthPart = Left(Source, 3)
    ' Take the last four characters
    YearPart = Right(Source, 4)

    MsgBox MonthPart & "-" & YearPart, vbOKOnly, "String Functions"
End Sub

Running the macro displays Jul-2015. Each function returns a new value and leaves the original string untouched, which is why the results can be combined safely in a single expression.

These functions all work on one string at a time. When a single value holds a whole list, two other functions handle it better.

How to Split and Join Strings in VBA

Data pasted into Excel often arrives as one long value with a separator between each item, such as “Pepsi,Coke,Fanta,Juice”. Reading that with Mid and InStr is slow and fragile. VBA provides a matched pair of functions for exactly this job.

Split takes a delimited string and returns a zero-based array, with one element per item. Join does the reverse, taking an array and returning a single string with a separator between the elements. Note that Join works on an array, not on two separate strings.

Sub SplitAndJoinDemo()
    Dim Source As String
    Dim Items() As String
    Dim i As Long

    Source = "Pepsi,Coke,Fanta,Juice"

    ' Break the value into an array on every comma
    Items = Split(Source, ",")

    For i = LBound(Items) To UBound(Items)
        Sheet1.Cells(i + 1, 1).Value = Items(i)
    Next i

    ' Rebuild the list with a different separator
    MsgBox Join(Items, " | "), vbOKOnly, "Joined List"
End Sub

The loop writes each drink into its own worksheet row, and the message box then shows Pepsi | Coke | Fanta | Juice. Three points make this pattern reliable in production code:

• Zero-based result: Split always returns an array numbered from 0, whatever Option Base is set to, so drive the loop with LBound and UBound rather than a fixed number.
• Declare as String(): Assign the result to a dynamic String array. Declaring a fixed size first causes a compile error.
• Empty input: Splitting an empty string returns an array with no elements, so test with UBound before reading Items(0).

Even with the correct function chosen, string code fails in a small number of predictable ways.

Common VBA String Errors and How to Fix Them

Most string bugs in Excel VBA come from a short list of causes. Recognising the symptom saves a long debugging session.

• Type mismatch on concatenation: A plus sign was used between text and a number. Replace it with an ampersand, which converts the number automatically.
• InStr returns 0: The substring was not found. InStr returns 0 rather than -1 on failure, so test for a value greater than zero before passing the result to Mid.
• Invalid procedure call in Mid or Left: The requested length is negative, or the start position is below 1. Guard the call with Len so the length never exceeds the string.
• Comparisons that should match but do not: Trailing spaces from imported data break equality. Wrap both sides in Trim, and use StrComp when case should be ignored.
• Text that will not become a number: A value such as “1,200” fails in arithmetic because of the separator. Strip it with Replace before converting with CLng.

Download Excel containing above code

Download the above Excel Code

FAQs