VBA String Functions: Left, Mid, InStr, Replace, Split and More

📎 This article includes 1 downloadable practice file ↓

Exports from ERP systems, banks and web tools arrive full of text that needs fixing: codes with prefixes, names in capitals, extra spaces, “INV-2026-0045” where you only need the number. VBA’s string functions handle all of it, and once you know about a dozen of them you can clean almost anything.

The core functions

Find, then cut

Most real cleaning combines InStr with Left/Mid/Right:

email = "asha.sharma@company.in"
user   = Left$(email, InStr(email, "@") - 1)           ' asha.sharma
domain = Mid$(email, InStr(email, "@") + 1)            ' company.in

path = "D:\Reports\2026\October\Sales.xlsx"
fileName = Mid$(path, InStrRev(path, "\") + 1)          ' Sales.xlsx
folder   = Left$(path, InStrRev(path, "\"))             ' D:\Reports\2026\October\

Split and Join

Dim parts As Variant
parts = Split("Asha Sharma, Delhi, 110001", ",")
city = Trim$(parts(1))                 ' Delhi
pin  = Trim$(parts(UBound(parts)))     ' 110001
Debug.Print Join(parts, " | ")

Pattern checks with Like

If code Like "INV-####" Then ...                     ' INV- followed by 4 digits
If pan Like "[A-Z][A-Z][A-Z][A-Z][A-Z]####[A-Z]" Then ... ' PAN format
If name Like "*Pvt*Ltd*" Then ...                     ' contains both words

# is any digit, ? any character, * anything, [A-Z] a range.

Format: numbers and dates to text

Format(1250.5, "#,##0.00")           ' 1,250.50
Format(Date, "dd-mmm-yyyy")          ' 05-Oct-2026
Format(7, "000")                     ' 007  (leading zeros for codes)

A complete cleaning macro

Sub CleanCustomerList()
    Dim ws As Worksheet, r As Long, nm As String, gst As String
    Set ws = ThisWorkbook.Worksheets("Customers")
    For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        nm = Application.WorksheetFunction.Trim(ws.Cells(r, "A").Value)  ' also squeezes double spaces
        ws.Cells(r, "A").Value = StrConv(nm, vbProperCase)
        gst = UCase$(Replace(ws.Cells(r, "B").Value, " ", ""))
        ws.Cells(r, "B").Value = gst
        If Len(gst) = 15 Then
            ws.Cells(r, "C").Value = Mid$(gst, 3, 10)                   ' PAN
            ws.Cells(r, "D").Value = Left$(gst, 2)                      ' state code
        Else
            ws.Cells(r, "C").Value = "Check GSTIN"
        End If
    Next r
End Sub

Note WorksheetFunction.Trim: VBA’s own Trim removes spaces only at the ends, while Excel’s TRIM also turns double spaces inside the text into single ones.

Where people go wrong

• Invisible characters — non-breaking spaces (Chr(160)) from web copies aren’t removed by Trim. Replace(s, Chr(160), " ") first.
• Numbers stored as text after cleaning — convert with CDbl() or Val() before writing back.
• Case-sensitive InStr — pass vbTextCompare: InStr(1, s, "gst", vbTextCompare).

Practice

The workbook has a messy customer export (names in capitals, spaces inside GSTINs, emails, file paths) and buttons for each cleaning step, with expected results to compare against.

Next: arrays — the technique that makes all of these macros dramatically faster.