Strings, dates and number formatting

Split, Join and Replace text, format numbers and dates reliably, and avoid the locale traps that silently corrupt data.

The string functions you actually use

Sub Strings()
    Dim s As String
    s = "  Ada, Lovelace , 1815  "

    Debug.Print Len(s)                       ' length, including spaces
    Debug.Print Len(Trim$(s))                ' Trim removes leading and trailing spaces
    Debug.Print UCase$(s), LCase$(s)
    Debug.Print Left$(s, 3), Right$(s, 4), Mid$(s, 4, 5)
    Debug.Print InStr(s, ",")                ' 1-based position, 0 when absent
    Debug.Print InStrRev(s, "a")             ' search from the end
    Debug.Print Replace(s, "  ", " ")        ' replace all occurrences
    Debug.Print String$(3, "-")              ' ---
    Debug.Print Space$(5) & "|"
    Debug.Print StrReverse("abc")            ' cba
    Debug.Print StrComp("Ada", "ada", vbTextCompare)   ' 0: equal, case-insensitive

    ' Split and Join: the standard way to parse and rebuild delimited text
    Dim parts() As String
    parts = Split("ada,grace,alan", ",")
    Debug.Print UBound(parts)                ' 2
    Debug.Print Join(parts, " | ")

    ' Split with a limit: the remainder stays in the last element
    Dim pair() As String
    pair = Split("key=value=extra", "=", 2)
    Debug.Print pair(0), pair(1)             ' key   value=extra

    ' use the $ variants in hot loops: they return a String, not a Variant
    Dim i As Long, acc As String
    For i = 1 To 1000
        acc = acc & CStr(i) & ","
    Next i
    Debug.Print Len(acc)
End Sub

• Use Mid$, Trim$, UCase$ and friends in loops. The plain versions return a Variant and add a conversion on every call.
• Concatenating in a loop allocates a new string each time. For very large output, build into an array and use Join, or write to a worksheet in one assignment.
• InStr returns 0 for not found, never a negative value, so If InStr(...) > 0 is the correct test.
• Comparison is case-sensitive by default. Pass vbTextCompare to StrComp, or use Option Compare Text at the top of the module to change the default for the whole module.

Dates without the locale surprises

Sub Dates()
    Dim d As Date
    d = DateSerial(2026, 9, 18)                   ' always unambiguous
    Debug.Print d
    Debug.Print Year(d), Month(d), Day(d)
    Debug.Print Hour(Now), Minute(Now)

    Debug.Print DateAdd("m", 1, d)                ' add a month
    Debug.Print DateAdd("d", -7, d)               ' a week earlier
    Debug.Print DateDiff("d", DateSerial(2026, 1, 1), d)
    Debug.Print DateDiff("m", DateSerial(2026, 1, 31), DateSerial(2026, 2, 28))

    Debug.Print Format$(d, "yyyy-mm-dd")          ' ISO: the safe interchange format
    Debug.Print Format$(d, "ddd dd mmm yyyy")
    Debug.Print Format$(d, "hh:nn:ss")            ' nn is minutes; mm is months
    Debug.Print Int(Now)                          ' today, without the time
    Debug.Print DateValue("2026-09-18")

    ' THE locale trap: CDate and IsDate interpret a string in the machine locale
    ' On a UK machine "03/04/2026" is 3 April; on a US machine it is 4 March.
    ' Both parse without error. Parse explicitly instead:
    Dim y As Long, m As Long, day As Long
    y = CLng(Split("2026-09-18", "-")(0))
    m = CLng(Split("2026-09-18", "-")(1))
    day = CLng(Split("2026-09-18", "-")(2))
    Debug.Print DateSerial(y, m, day)

    ' Comparing dates is numeric comparison of the underlying Double
    Debug.Print (DateSerial(2026, 1, 1) < DateSerial(2026, 12, 31))
End Sub

A worksheet cell holds a real date value. Read .Value2, not .Text: the text is already formatted for display and re-parsing it reintroduces the locale problem you were trying to avoid.

Numbers, rounding and formatting

Sub Numbers()
    Debug.Print Format$(1234.567, "#,##0.00")      ' 1,234.57  (banker's on .5)
    Debug.Print Format$(0.25, "0.0%")              ' 25.0%
    Debug.Print Format$(-5, "#,##0;(#,##0)")       ' (5)
    Debug.Print Format$(1234.5, "0.0E+00")
    Debug.Print Format$(True, "Yes/No")
    Debug.Print Format$(0, "000")                  ' 000

    ' Rounding: Round uses banker's rounding by default, which surprises people
    Debug.Print Round(2.5)          ' 2
    Debug.Print Round(3.5)          ' 4
    Debug.Print Round(2.5, 0, vbUp) ' 3   (or use FormatNumber / WorksheetFunction.Round)

    Debug.Print Int(2.7), Fix(-2.7)   ' 2 and -2: Int rounds down, Fix rounds toward zero
    Debug.Print Abs(-3), Sgn(-3), Sqr(16)

    ' Division: the backslash is integer division, the forward slash is not
    Debug.Print 7 / 2                ' 3.5
    Debug.Print 7 \ 2               ' 3
    Debug.Print 7 Mod 2              ' 1
    ' Mod of a negative number takes the sign of the dividend: -7 Mod 3 = -1

    ' Converter functions, plus the Val trap
    Debug.Print CLng("123"), CDbl("1.5"), CStr(123), CDate("2026-09-18")
    Debug.Print Val("12abc")         ' 12: stops at the first non-numeric character
    Debug.Print Val("abc")           ' 0: no error at all

    ' Use CDbl/CLng with the locale-independent converters when possible,
    ' and test with a comma decimal separator if any user may have one.
End Sub

FAQ

Related

Arrays, collections and dictionaries Macros, worksheets and ranges

Last refreshed 2026-09-18.