Wauvel

Excel like a finance pro.

← All VBA snippets

Take text apart and put it back together in code

Split a reference field into parts, reorder them, and rejoin them into something useful.

1What it does

`Split` turns a string into an array at every delimiter; `Join` turns an array back into a string with a delimiter between the pieces. Between them sits the actual work: pick the part you need, reorder the parts, trim them, drop an empty one. It's how you pull the invoice number out of "Acme Corp | INV-1042 | 2026-08-31", or build a filename like "2026-08 Acme INV-1042.pdf" from the pieces. Split's array starts at zero, which is the source of most off-by-one bugs in this kind of code.

2The code

Sub BuildFileNames()
    Dim ws As Worksheet, r As Long, lastRow As Long
    Dim parts() As String, cleaned() As String, i As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For r = 2 To lastRow
        ' "Acme Corp | INV-1042 | 2026-08-31"
        parts = Split(ws.Cells(r, 1).Value, "|")

        ' Guard against a malformed line before indexing into it.
        If UBound(parts) < 2 Then
            ws.Cells(r, 2).Value = "(expected 3 parts)"
        Else
            ' Split's array is ZERO-based: parts(0) is the customer.
            ReDim cleaned(0 To 2)
            For i = 0 To 2
                cleaned(i) = Trim$(parts(i))
            Next i

            ' Reorder and rejoin: date prefix, customer, invoice.
            ws.Cells(r, 2).Value = Join(Array( _
                Left$(cleaned(2), 7), cleaned(0), cleaned(1)), " ") & ".pdf"
            ws.Cells(r, 3).Value = cleaned(1)       ' just the invoice number
        End If
    Next r
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Pull one field out of a combined memo or reference line.
  • Build consistent filenames from a customer, a date and an invoice number.
  • Turn a delimited list in one cell into separate values to loop over.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Split(line, "|") — note the array starts at 0
parts(0)"Acme Corp "
parts(1)" INV-1042 "
parts(2)" 2026-08-31"

Join → "2026-08 Acme Corp INV-1042.pdf". The pieces had to be trimmed first — parts(0) still carries its trailing space.

5Pitfalls

Off by one: parts(1) is the SECOND piece, because Split's array starts at 0.

Fix: Index from 0, and use UBound(parts) for the last index rather than a count.

A line with fewer delimiters than expected raises "subscript out of range".

Fix: Check UBound before indexing, as above.

Pieces keep the spaces around the delimiter, so "Acme " won't match "Acme".

Fix: Trim each part after splitting.

Splitting on a multi-character delimiter like " | " misses lines that use "|" alone.

Fix: Split on the bare character and Trim, which handles both spacings.

6No-code alternatives

  • TEXTSPLIT / TEXTJOIN — The same operations as formulas, which recalculate and need no macro.
  • Text to Columns — When the goal is simply to split a column into columns, no code is needed. See the tip.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.