Text.Middle Function in Power Query M (4 Easy Examples)

Text.Middle returns a section of text starting at a given position, either a set number of characters or everything through to the end. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to pull a chunk out of the middle of a text value, like a year segment buried inside a product code, this is the function you reach for.

Table of Contents

Syntax of Text.Middle Function

Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable text

where

text (required, nullable text). The text value you want to extract from.
start (required, number). The zero-based position to start at. The first character is index 0, the second is index 1, and so on.
count (optional, nullable number). How many characters to return. Omit it to get everything from start through to the end of the text.

Returns: a text value containing the extracted characters. If the requested range runs past the end of the text, it returns whatever characters are available, with no error.

In plain terms, you tell it where to start and how many characters to grab, and it hands back that slice of the text.

Example 1: Grab a fixed number of characters from a position

Pull five characters out of the word Spreadsheet, starting at position 6.

Text.Middle("Spreadsheet",6,5)

Result: sheet

Because start is zero-based, position 6 lands on the seventh character. Here is the index for each letter:

char	S	p	r	e	a	d	s	h	e	e	t
index	0	1	2	3	4	5	6	7	8	9	10

Index 6 is the second s, and counting five characters from there gives sheet.

Example 2: Omit count to get the rest of the text

Leave off the count argument and you get everything from start onward.

Text.Middle("PowerQuery",5)

Result: Query

Index 5 is the Q, so the function returns every character from there to the end. No need to work out how many characters are left.

Example 3: A count past the end returns what is available

Ask for more characters than the text has, and Text.Middle gives you whatever remains instead of erroring.

Text.Middle("Excel",3,20)

Result: el

Starting at index 3 (the second e) leaves only two characters, so a count of 20 simply returns el.

Example 4: Extract the year segment from a product code

A common real-world use is pulling a fixed-position segment out of a structured code across a whole column.

Say you have a Products query where every code follows the pattern PRD-YYYY-CATEGORY-NNN.

You want a Year column holding just the four-digit year.

Here is the starting data:

ProductCode
PRD-2024-ELEC-018
PRD-2023-HOME-204
PRD-2025-TOYS-077

The year starts at index 4 (right after PRD-) and runs four characters, so use Table.AddColumn with Text.Middle([ProductCode],4,4):

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Result = Table.AddColumn(Source,"Year",each Text.Middle([ProductCode],4,4),type text)
in
    Result

This reads four characters from index 4 of each code.

The result adds the year for every row:

ProductCode	Year
PRD-2024-ELEC-018	2024
PRD-2023-HOME-204	2023
PRD-2025-TOYS-077	2025

Because the codes contain letters, Power Query keeps the extracted year as text rather than converting it to a number.

Things to keep in mind with Text.Middle

start is zero-based, unlike Excel’s MID which is one-based. This is the number one trap when porting an Excel formula. The first character is index 0, so Text.Middle("Spreadsheet",6,5) returns sheet where Excel’s MID("Spreadsheet",7,5) uses 7. Rule of thumb: Power Query start equals the Excel MID start minus one.
Omit count to grab everything from start to the end. You do not have to compute the remaining length yourself.
An out-of-range count never errors. If count is larger than the characters left, you get whatever is available. If start is at or past the end, you get an empty string "".
Pair it with a text check for dynamic extraction. When the segment is not at a fixed index, confirm a substring is present first with Text.Contains, then work out the start position to feed into Text.Middle.
A negative start throws. Text.Middle("Excel",-1) raises Expression.Error. The offset and the count must both be non-negative.

Common questions about Text.Middle

How do I do an Excel MID in Power Query?

Use Text.Middle, but subtract 1 from the Excel start position because Power Query counts from 0. So MID(A1,7,5) becomes Text.Middle([Column],6,5).

What is the difference between Text.Middle and Text.Range?

Both take a text value, a zero-based start, and an optional count. The difference is at the edges: Text.Range throws an error when the requested range runs past the end of the text, while Text.Middle forgivingly returns whatever is available. Use Text.Middle when the length is unpredictable, and Text.Range when you want a hard failure on bad input.

List of All Power Query Functions

Related Power Query Functions / Articles: