Excel Power Query Left Function: How to Extract Text

Formulas & functions · Updated November 18, 2026

In Excel Power Query, the equivalent of the worksheet LEFT function is Text.Start(text, count): it returns the first specified number of characters from a text value. For example, use Text.Start([ProductCode], 3) in a Power Query custom column to keep the first three characters of each value in ProductCode.

What “Left” Means in Power Query

Power Query does not use Excel’s worksheet function name LEFT for this operation. Power Query formulas are written in the M language, where the corresponding function is named Text.Start. The word “Start” describes the beginning of the text, which is the left-hand side when text is displayed in its usual direction.

This difference matters because worksheet formulas and Power Query expressions run in different places. A worksheet formula recalculates in a cell when its inputs change. A Power Query expression becomes part of a sequence of query steps that shape data as it is imported or refreshed. You can use the same idea in both places, but you cannot paste the M function name into a worksheet cell and expect it to work as an Excel formula.

Power Query is useful when you need to perform the same cleanup each time a table is refreshed. If a monthly file contains product codes, dates, or categories that need a consistent prefix extracted, the query can repeat that transformation on future data instead of requiring you to fill a formula down again.

Syntax and How the Function Works

The basic M expression is Text.Start(text, count). The first argument is the text to examine; the second is the number of characters to return from its beginning. The count is a character count, not a position number: a count of 3 returns characters one, two, and three.

In the Power Query Custom Column dialog, enter an M expression without a leading equals sign. That differs from a normal worksheet formula, which begins with =. The expression is evaluated for each row, so a column reference in square brackets refers to the value in the current row.

  • Text.Start([Item], 2) returns the first two characters of the Item value.
  • Text.Start([PostalCode], 5) returns up to the first five characters of PostalCode.
  • Text.Start([Description], 1) returns the first character, if the value has one.

If the requested count is larger than the text value, the result is the available text rather than extra characters being added. For example, a three-character value remains three characters when you request its first eight. A negative count is not a useful extraction length and can produce an error, so use a nonnegative whole number. An empty string has no characters to return.

Extract First Characters with the Power Query Ribbon

You can use the interface without typing M yourself. In Excel for Microsoft 365 on Windows, select a cell in your source table and choose Data > From Table/Range to open the Power Query Editor. If prompted, confirm that the table has headers. Then select the text column you want to shorten.

  1. On the Transform tab, open Extract and choose First Characters.
  2. Enter the number of characters to keep and confirm.
  3. Check the preview to make sure the results match the intended pattern.
  4. Choose Home > Close & Load to return the result to Excel.

That command transforms the selected column. If you need to preserve the original values as well, look for the corresponding Extract option on the Add Column tab and choose First Characters there, where available in your editor layout. It creates a derived column instead of replacing the selected one. Ribbon labels and placement can vary slightly by Power Query build or display language, but the operation is called First Characters.

After loading, Power Query records the extraction as an applied step. To change the number of characters later, select the relevant step in Query Settings and edit or remove it, or repeat the extraction with the correct count. On Excel for Mac and Excel for the web, Power Query availability and editing features can differ from Windows; when the editor is available, the M expression works the same way, though ribbon placement may differ.

Build a Custom Column with Text.Start

A custom column gives you more control than the ribbon command. With a table containing ProductCode values such as AB-4821 and XY-9070, you might want a two-letter category prefix. Open the query, choose Add Column > Custom Column, give the new column a name such as Prefix, and enter Text.Start([ProductCode], 2). The preview should show AB and XY in the new column.

The expression above assumes ProductCode is already a text column. If it is a number, Power Query cannot treat it as text automatically in every context. Convert it explicitly with an expression such as Text.Start(Text.From([OrderID]), 3). This takes the first three characters of the text representation of OrderID. If OrderID is 48219, the result is 482.

Be careful with identifiers that can begin with zero. If a postal code or account code such as 00482 is imported as a number, the leading zeros may already be lost before extraction. Converting 482 back to text produces 482, not 00482. Set the source column’s type to Text early in the query, or correct the source data, if those zeros are meaningful.

You can also combine extraction with other M operations. For example, if a code has a fixed two-character prefix followed by a category character, Text.Middle([ProductCode], 2, 1) returns one character beginning at position 2. M text positions start at zero, so position 2 means the third character. This is useful when you need a character from the middle rather than the beginning.

Choose the Right Text Operation

Text.Start is best when the number of leading characters is fixed. If you need characters from the end, use Text.End. If you need a segment beginning at a particular zero-based position, use Text.Middle. For example, these functions can separate a fixed-format code into a prefix, a central marker, and a suffix.

Fixed character counts are not always the same as extracting a meaningful field. If a product code contains a variable-length prefix separated by a hyphen, taking the first three characters may cut through a longer prefix or include part of the next field. In that situation, split on the delimiter instead: select the column and use Split Column by a delimiter, choosing the hyphen and the appropriate split option. Delimiter-based extraction is usually more robust when field lengths vary.

There is also a useful distinction between “first characters” and “first word.” Text.Start counts every character, including spaces and punctuation. If a description begins with a space, that space counts toward the requested length. Trim unwanted spaces first if they are accidental, or use a delimiter-based approach if the desired boundary is a word or symbol.

Common Errors and Data-Type Issues

A frequent problem is applying Text.Start to a column containing numbers, dates, or mixed values. Text.Start is a text operation, so check the column’s data type in the Power Query Editor. You can select the column and use Transform > Data Type > Text, or convert a particular value with Text.From in a custom expression. Converting a date to text can depend on formatting and locale, so decide what date representation you actually want before taking its first characters.

Nulls and empty strings are different. A null means the value is missing; an empty string is text containing zero characters. Text.Start returns null for a null text value, while an empty string stays empty. If your source contains errors or unexpected non-text values, inspect those rows before loading. Replacing errors with blanks without understanding their cause can hide a problem in the source.

Another common issue is assuming that a count refers to the last position to include. It does not: the count is how many characters to return. Requesting 4 returns up to four characters, not characters from position 1 through position 4 after some separate offset. For an offset-and-length operation, use Text.Middle and remember that its starting position is zero-based.

Power Query Text.Start vs. Excel LEFT

Excel’s worksheet function is =LEFT(text, [num_chars]). In Power Query, the related M function is Text.Start(text, count). Both return leading characters, but their syntax, context, and refresh behavior differ. Excel’s LEFT is entered in a worksheet cell; Text.Start is entered as an M expression in the query editor or appears behind a Power Query interface action.

Use the worksheet function when you want a formula result alongside worksheet data and are comfortable maintaining the formula in the sheet. Use Power Query when the extraction is part of a repeatable import or cleanup process, especially when new rows arrive on refresh. A query may load the transformed result to a table, but the transformation itself is not a formula stored in each output cell.

It is therefore a misconception that Power Query has a function literally named LEFT. Searching for that name can lead to worksheet instructions that do not work in the query editor. The reliable translation is: worksheet LEFT corresponds to Power Query Text.Start for taking characters from the beginning.

Refresh and Maintain the Transformation

Once the query is loaded, the extraction is repeated when you refresh the query, provided the source data and query structure still match. In Excel for Windows, use Data > Refresh All to refresh workbook queries. If a source column is renamed or removed, the step that refers to it may fail; the column reference in a custom expression must match the actual query column name.

For a query that other people will maintain, give the output column a descriptive name such as ProductPrefix rather than Column1. Keep the source column when there is any chance you will need to audit or change the extraction rule. Also consider documenting why the count is fixed—for example, because the first three characters identify a region. That makes it easier to spot when a new source format invalidates the assumption.

Frequently Asked Questions

Is there a LEFT function in Power Query?
No. The M function for returning characters from the beginning of text is Text.Start. Excel worksheet formulas use the separate function LEFT.

How do I take the first three characters in Power Query?
In a custom column, enter Text.Start([ColumnName], 3), replacing ColumnName with the actual text column. In the ribbon, select the column and choose Transform > Extract > First Characters, then enter 3.

Does Text.Start count from zero?
The count says how many characters to return, so a count of 3 returns the first three characters. Zero-based indexing applies to the starting position argument of functions such as Text.Middle, not to Text.Start’s count.

What if the text is shorter than the number I request?
Power Query returns the characters that exist. It does not add padding to reach the requested length. For example, requesting five characters from a two-character value returns the two available characters.

Can I use Text.Start on a number column?
Convert the value to text first, for example with Text.Start(Text.From([OrderID]), 3). For codes where leading zeros matter, make sure the source was treated as text before numeric conversion could remove them.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.