📎 This article includes 1 downloadable practice file ↓

📘 Excel VBA Course · Lesson 8 of 15 — see all lessons

Exports from SAP, Oracle and web tools are full of text that needs cleaning: codes with prefixes, names in capitals, extra spaces. VBA’s string functions handle all of it.

The essentials

Split: text into pieces

Dim parts() As String
parts = Split("INV-2025-001", "-")
Debug.Print parts(0)   ' INV
Debug.Print parts(1)   ' 2025
Debug.Print UBound(parts)   ' 2 (index of the last piece)

Join: pieces back into text

Dim names(2) As String
names(0) = "Asha": names(1) = "Rohit": names(2) = "Meera"
Range("A1").Value = Join(names, ", ")    ' Asha, Rohit, Meera

Concatenate with &

msg = "Hello " & firstName & ", your total is " & Format(total, "#,##0.00")

Always use & to join text. + tries to add when one side is a number and gives “Type mismatch”.

Real example: clean an export column

Sub CleanCustomerNames()
    Dim c As Range, s As String
    For Each c In Range("B2", Cells(Rows.Count, "B").End(xlUp))
        s = Replace(c.Value, Chr(160), " ")    ' non-breaking spaces from web/ERP exports
        s = Application.WorksheetFunction.Trim(s)   ' also collapses double spaces
        c.Value = StrConv(s, vbProperCase)
    Next c
End Sub

Extract the text between two characters

Function Between(s As String, a As String, b As String) As String
    Dim p1 As Long, p2 As Long
    p1 = InStr(s, a): If p1 = 0 Then Exit Function
    p1 = p1 + Len(a)
    p2 = InStr(p1, s, b): If p2 = 0 Then Exit Function
    Between = Mid(s, p1, p2 - p1)
End Function
' =Between(A2, "(", ")")  →  text inside brackets

Case-insensitive search

If InStr(1, c.Value, "urgent", vbTextCompare) > 0 Then c.Font.Bold = True

Try it yourself: step by step

1. On the Data sheet, type some rep names in column D with extra spaces and capitals (e.g. “ ASHA ”).
2. Run L08_CleanNames — names become “Asha”.
3. In the Immediate window try ? Split("INV-20250001", "-")(1) → 20250001.
4. Write a function that returns the region code as the first letter of the region: =Left(C2, 1) in VBA is Left(c.Value, 1).
5. Test the text functions live in the Formula Lab text tab.