Downloads
Power Query MSplitters and combinersIntermediate

Splitter.SplitTextByPositions

Returns a splitter that cuts text at fixed character positions, for fixed-width files and layouts without delimiters.

Syntax
Splitter.SplitTextByPositions(positions)

Returns: A function that you pass to a split step. It returns a list of pieces.

What it does

Some reports have no delimiter at all. Each field occupies a set number of characters, which is called fixed width. Splitter.SplitTextByPositions cuts the text at the character positions you list. Positions count from zero, so position 0 is the first character.

Syntax and arguments

Example

A legacy ledger export writes each line as a date of eight characters, an account code of four characters and an amount. The date is in characters 0 to 7, the account in 8 to 11, and the rest is the amount.

let
    Line = "20260331100012500",
    Pieces = Splitter.SplitTextByPositions({0, 8, 12})(Line),
    Date = Pieces{0},
    Account = Pieces{1},
    Amount = Pieces{2}
in
    Amount

Pieces is {"20260331", "1000", "12500"}, so Amount is the text "12500". The conversion to a number and a decimal point would be a separate step.

Example: a second, different use

Applying the same positions to a table column with Table.SplitColumn, so each fixed-width field becomes its own column.

Things to watch

In business terms

Mainframe reports, older general ledger exports and government or bank formats are often fixed width. The layout is usually documented in the system guide, and a reviewer can check a few lines by hand against the positions.

Related functions

All Power Query M functions · View as Markdown · M basics