Splitter.SplitTextByPositions in Power Query M (5 Examples)

If you want to split text at specific character positions instead of by a delimiter, Splitter.SplitTextByPositions is the function for that. It is built for fixed-width data, where each chunk starts at a known offset. Think product codes, zip groupings, or any string carved up by position.

Table of Contents

Syntax of Splitter.SplitTextByPositions Function

Splitter.SplitTextByPositions(positions as list, optional startAtEnd as nullable logical) as function

where

positions (required, list). The 0-based character offsets where each chunk starts. {0,3} means the first chunk starts at index 0 and the second starts at index 3.
startAtEnd (optional, nullable logical). When true, the positions are counted from the end of the string. Omitted or null behaves as false, counting from the start.

Returns: a function that takes a text value and returns a list of text chunks split at the given positions.

This one trips people up at first. Splitter.SplitTextByPositions does not split anything on its own. It hands you back a splitter function. You then either call that function on your text, or pass it to Table.SplitColumn to carve up a whole column.

Example 1: Split text into two chunks

The simplest case. Pass your positions, then call the returned function on a string. Here {0,3} says the first chunk starts at index 0 and the second starts at index 3.

let
    Source = Splitter.SplitTextByPositions({0,3})("ABCDEF")
in
    Source

Result: {“ABC”, “DEF”}

The first chunk runs from position 0 up to position 3, and the second chunk takes everything from position 3 onward.

Example 2: Split a product code into three parts

Add more positions to get more chunks. The number of chunks always matches the number of positions you supply. Here {0,2,5} carves the code into three pieces.

let
    Source = Splitter.SplitTextByPositions({0,2,5})("NY12ABCD")
in
    Source

Result: {“NY”, “12A”, “BCD”}

Each position marks where a new chunk starts. The last chunk grabs everything from position 5 to the end.

Example 3: Count positions from the end with startAtEnd

Set startAtEnd to true and the positions are measured from the right edge of the string. This is handy for grabbing the last few characters without knowing the total length, like a phone number’s last four digits.

let
    Source = Splitter.SplitTextByPositions({0,4},true)("5551234567")
in
    Source

Result: {“555123”, “4567”}

The string is 10 characters long, so position 4 from the end lands at index 6. The split there groups the trailing four digits on their own. If you later need to restore leading zeros on a chunk that lost them, Text.PadStart handles that.

Example 4: Split a fixed-width column with Table.SplitColumn

This is where the function really earns its keep. Table.SplitColumn accepts a splitter function, so you feed it Splitter.SplitTextByPositions to break a fixed-width column into named columns in one step.

Say you have a Codes column where each code packs a city, year, and item into fixed positions:

Code
NYC2024AB
LAX2023CD

Pass the splitter and a list of new column names to Table.SplitColumn. Positions {0,3,7} split each code at index 0, 3, and 7.

let
    Source = Excel.CurrentWorkbook(){[Name="Codes"]}[Content],
    Split = Table.SplitColumn(Source,"Code",Splitter.SplitTextByPositions({0,3,7}),{"City","Year","Item"})
in
    Split

Here is the result:

City	Year	Item
NYC	2024	AB
LAX	2023	CD

Notice you never call the splitter yourself here. Table.SplitColumn calls it once per row, which is exactly why the function returns a function rather than splitting on the spot.

Example 5: A position beyond the string length

If a position lands at or past the end of the string, you get an empty chunk "" instead of an error. The chunk count still matches your positions, so short strings will not break a column split.

let
    Source = Splitter.SplitTextByPositions({0,5,8})("AB123")
in
    Source

Result: {“AB123”, “”, “”}

The string is only 5 characters long, so positions 5 and 8 both fall at or past the end. Those two chunks come back empty.

Things to keep in mind with Splitter.SplitTextByPositions

It returns a function, not a list. You have to call that function on your text, like Splitter.SplitTextByPositions({0,3})("ABCDEF"), or pass it to Table.SplitColumn. Stop after the first set of parentheses and you are left holding a function value.
positions are 0-based offsets where each chunk starts, not chunk lengths. For chunks of length 3, 4, and 2 you write cumulative starts {0,3,7}, not {3,4,2}. If you want to work with lengths instead, use the sibling Splitter.SplitTextByLengths.
Its real home is Table.SplitColumn for fixed-width data. That is the main reason to reach for it over Text.Range or manual slicing. When your text has clear markers instead of fixed positions, reach for delimiter-based splitting instead.
A position at or beyond the string length gives you an empty trailing chunk "", never an error. The chunk count stays consistent even when source strings are shorter than expected.
startAtEnd set to true flips counting to the right edge of the string. That makes “last N characters” groupings easy without knowing the total length.

Common questions about Splitter.SplitTextByPositions

What is the difference between Splitter.SplitTextByPositions and Splitter.SplitTextByLengths?

Splitter.SplitTextByPositions takes the offsets where each chunk starts, so you give it cumulative starting points like {0,3,7}. Splitter.SplitTextByLengths takes the length of each chunk instead, so the same split would be {3,4,2}. Pick whichever matches how you think about your data.

How do I use it to split a column?

Pass the splitter as the third argument of Table.SplitColumn and give a list of new column names as the fourth. For example, Table.SplitColumn(Source,"Code",Splitter.SplitTextByPositions({0,3,7}),{"City","Year","Item"}) splits the Code column into three named columns.

List of All Power Query Functions

Related Power Query Functions / Articles: