Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 37 ยท Language Basics

Working with Text: VBA String Functions

Master VBA text handling: Len, Left, Right, Mid, InStr, Replace, Split, Join, Trim, UCase, StrComp, Format and string building with practical examples.

Most real-world VBA work involves text: cleaning names, splitting addresses, building file names. VBA ships with a rich set of string functions. They never change the original string; they return a new one, so you assign the result.

Length, case and trimming

VB
Sub BasicText()
    Dim s As String
    s = "  Hello, VBA!  "
    Debug.Print Len(s)          ' 15
    Debug.Print Trim(s)         ' Hello, VBA!
    Debug.Print LTrim(s)        ' removes leading spaces only
    Debug.Print RTrim(s)        ' removes trailing spaces only
    Debug.Print UCase(s)        ' HELLO, VBA!
    Debug.Print LCase(s)        ' hello, vba!
End Sub

Trim only removes spaces, not tabs or line breaks. For other characters use Replace.

Taking pieces: Left, Right, Mid

VB
Dim code As String
code = "INV-2026-0042"
Debug.Print Left(code, 3)       ' INV
Debug.Print Right(code, 4)      ' 0042
Debug.Print Mid(code, 5, 4)     ' 2026  (start at 5, take 4)
Debug.Print Mid(code, 10)       ' 0042  (omit length: to the end)

Positions start at 1, not 0. Mid can also be used on the left of = to overwrite characters in place: Mid(code, 1, 3) = "BIL".

Searching: InStr, InStrRev

InStr(start, text, find) returns the position of the first match or 0 if none:

VB
Dim email As String, at As Long
email = "[email protected]"
at = InStr(email, "@")
If at > 0 Then
    Debug.Print Left(email, at - 1)     ' ada
    Debug.Print Mid(email, at + 1)      ' example.com
End If
Debug.Print InStr(1, "Banana", "AN", vbTextCompare)   ' 2 (ignores case)
Debug.Print InStrRev("a/b/c.txt", "/")                ' 4 (last slash)

Remember: the optional start comes first in InStr, but the compare mode comes last, a notorious inconsistency.

Replacing and repeating

VB
Debug.Print Replace("a-b-c", "-", "/")            ' a/b/c
Debug.Print Replace("aaaa", "a", "b", 1, 2)       ' bbaa  (start 1, replace 2 times)
Debug.Print String(5, "*")                         ' *****
Debug.Print Space(3) & "|"                         '    |
Debug.Print StrReverse("stressed")                 ' desserts

The fourth parameter of Replace is the start position and the fifth the count. Careful: if the start is greater than 1, the text before it is dropped from the result.

Split and Join

Split turns a delimited string into an array; Join does the opposite.

VB
Dim parts() As String, i As Long
parts = Split("red,green,blue", ",")
For i = LBound(parts) To UBound(parts)
    Debug.Print i & ": " & parts(i)
Next i
Debug.Print Join(parts, " | ")      ' red | green | blue

The array from Split is zero-based, whatever Option Base says. See Arrays.

Comparing

  • = compares exactly, case sensitive (unless Option Compare Text).
  • StrComp("a", "A", vbTextCompare) returns 0 for equal, -1 or 1 for less or greater.
  • LCase(a) = LCase(b) is the simple way to ignore case.

Characters and codes

Asc("A") is 65, Chr(65) is A. Useful constants: vbCrLf (line break), vbTab, vbNewLine, and Chr(34) for a double quote (or "" inside a literal).

VB
Debug.Print "Line1" & vbCrLf & "Line2"
Debug.Print "He said " & Chr(34) & "ok" & Chr(34)

Format

Format(value, pattern) produces formatted text:

VB
Debug.Print Format(1234.5, "#,##0.00")        ' 1,234.50
Debug.Print Format(0.256, "0.0%")             ' 25.6%
Debug.Print Format(Date, "yyyy-mm-dd")        ' e.g. 2026-11-12
Debug.Print Format(5, "000")                  ' 005
Debug.Print Format("hello", ">")              ' HELLO

Month and minute both use m; VBA decides by context (mm after h means minutes). For clarity use nn for minutes: Format(Now, "hh:nn").

Stand-in: the same operations in JavaScript

This JavaScript stand-in (not VBA) implements a few helpers with VBA semantics (1-based positions) so you can experiment with email splitting and padding.

JS Stand-in: Left, Mid, InStr, Split

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A worked example: tidy a full name

VB
Function ProperName(ByVal full As String) As String
    Dim words() As String, i As Long
    words = Split(Trim(full), " ")
    For i = LBound(words) To UBound(words)
        If Len(words(i)) > 0 Then
            words(i) = UCase(Left(words(i), 1)) & LCase(Mid(words(i), 2))
        End If
    Next i
    ProperName = Join(words, " ")
End Function

ProperName(" aDA loVELACE ") returns Ada Lovelace (empty entries from double spaces stay as empty words). StrConv(text, vbProperCase) does a similar job in one call.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Mid("ABCDEF", 2, 3) return?

    Start at position 2, take 3 characters: B, C, D.
  2. 2. What does InStr return when the text is not found?

    InStr returns 0 for no match, because positions start at 1.
  3. 3. Which function breaks "a,b,c" into an array?

    Split returns a zero-based array of the pieces.
  4. 4. How do you type a double quote inside a VBA string literal?

    Inside a literal, two double quotes represent one.

Summary

  • Len, Trim, UCase, LCase basics; Left, Right, Mid slice by 1-based position.
  • InStr finds, Replace substitutes, Split and Join convert to and from arrays.
  • Format controls numbers, percentages and dates.
  • Strings are immutable: always assign the result.

Next: Math and Date Functions