Cutting Text: Left, Mid and Right

Three functions, one idea

Every text-cleaning job starts the same way: take a piece out of a longer string. VBA gives you three functions for it, and they behave exactly like their worksheet cousins.

• Left(text, n) — the first n characters.
• Right(text, n) — the last n characters.
• Mid(text, start, n) — n characters beginning at position start, counting from 1. Leave n off and you get everything to the end.
• Len(text) — how many characters there are altogether.

Sub CutParts()
    Dim ref As String

    ref = "CH-2026-04871"

    Debug.Print Left(ref, 2)      ' CH
    Debug.Print Mid(ref, 4, 4)    ' 2026
    Debug.Print Right(ref, 5)     ' 04871
    Debug.Print Mid(ref, 4)       ' 2026-04871
    Debug.Print Len(ref)          ' 13
End Sub

A macro you can run right now

This one needs nothing prepared: it builds its own sample sheet, splits a column of reference codes into three columns, and flags anything that does not fit the expected shape. Open the VBA editor with Alt+F11, choose Insert → Module, paste the code in and press F5.

Option Explicit

Sub SplitReferenceCodes()
    Dim ws As Worksheet
    Dim samples As Variant
    Dim out() As Variant
    Dim ref As String
    Dim r As Long

    samples = Array("CH-2026-04871", "DE-2026-11250", "CH-2025-00042", _
                    "AT-2026-93117", "CH-26-4871", "FR-2026-70008")

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Ref Demo")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "Ref Demo"
    End If

    ws.Cells.Clear
    ws.Range("A1:D1").Value = Array("Reference", "Country", "Year", "Number")
    ws.Range("A1:D1").Font.Bold = True

    ReDim out(1 To UBound(samples) + 1, 1 To 4)

    For r = 0 To UBound(samples)
        ref = CStr(samples(r))
        out(r + 1, 1) = ref

        If Len(ref) = 13 Then
            out(r + 1, 2) = Left(ref, 2)
            out(r + 1, 3) = Mid(ref, 4, 4)
            out(r + 1, 4) = Right(ref, 5)
        Else
            out(r + 1, 2) = "CHECK: " & Len(ref) & " characters"
        End If
    Next r

    ws.Range("A2").Resize(UBound(out, 1), 4).Value = out
    ws.Columns("A:D").AutoFit
    ws.Activate
End Sub

Line by line

• samples = Array(...) keeps the test data inside the macro, so there is nothing to download. Later you replace this one line with a read from your own column.
• The On Error Resume Next pair asks whether the sheet already exists without stopping the macro when it does not; If ws Is Nothing then creates it. This get-or-create block appears in almost every macro you will write.
• ReDim out(1 To UBound(samples) + 1, 1 To 4) — one row per sample, four columns. Array() counts from 0 and the sheet counts from 1, which is where the + 1 comes from.
• If Len(ref) = 13 is the guard. Cutting at fixed positions is only correct while every value has exactly the same shape.
• Left(ref, 2), Mid(ref, 4, 4), Right(ref, 5) pull out country, year and number. To fit your own layout you change these three numbers and nothing else.
• The Else branch writes what is wrong and how long the value really was, instead of silently producing a wrong year.
• ws.Range("A2").Resize(...).Value = out writes the whole block in one go, which is far quicker than filling cell by cell.
• ThisWorkbook means the workbook that holds the code. If you keep your macros in PERSONAL.XLSB, swap it for ActiveWorkbook or the sheet appears in the hidden personal file.

The trap: you cut what is stored, not what you see

Left, Mid and Right want text. Hand them a cell and VBA converts the cell's underlying value to text first — and that value is rarely the string on screen. A cell showing 13.09.2026 holds a date, so Left(oneCell.Value, 2) returns whatever your Windows short-date setting produces: 13 in Zurich, 9/ in Chicago. A cell showing 1'234.50 holds the number 1234.5.

Dim oneCell As Range

Set oneCell = ThisWorkbook.Worksheets("Ref Demo").Range("A2")

Debug.Print Left(oneCell.Value, 2)                        ' the stored value
Debug.Print Left(oneCell.Text, 2)                         ' what is displayed
Debug.Print Left(Format(oneCell.Value, "yyyy-mm-dd"), 4)  ' what you meant

.Text looks like the fix and then costs you the afternoon: it returns exactly what the cell displays, so on a column too narrow for a date or a number it hands you #####. Column widths are not a data format. Cut a string you produced yourself — Format() with a pattern you chose, or CStr() — and the machine it runs on stops mattering.

How this course goes on

Fixed positions hold up only as long as the data does. Lesson 2 finds the separator with InStr, so the cutting position comes out of the text itself instead of being typed in; lesson 4 cuts on every separator at once with Split. The rest of the course covers stripping stray spaces and non-printing characters, swapping substrings with Replace, pattern-matching with Like, joining thousands of pieces without the usual slowdown, and turning numbers and dates back into formatted text with Format.