To extract text in Excel Power Query, select the relevant column and use Transform > Extract to choose an option such as text before or after a delimiter. For more control, add a custom column with an M expression such as Text.AfterDelimiter([Code], "-"), which returns the text after a hyphen in the Code column.
What Power Query extraction does
Power Query is Excel’s tool for importing, reshaping, and refreshing data. Instead of editing each cell with a worksheet formula, you define a transformation in the query. When the source data changes, refreshing the query applies that transformation to the updated rows. This is useful for recurring reports, downloaded files, and other datasets where text follows a reasonably consistent pattern.
“Extract text” can mean several different tasks. You might need characters before a separator, a value between two markers, a fixed number of characters, or only certain characters such as digits. Power Query has separate commands and M functions for these cases. Choosing the right method depends on what defines the part you want: a delimiter, a fixed position, or a set of allowed characters.
These instructions use Microsoft Excel for Microsoft 365 on Windows. Power Query features and authoring options can differ in Excel for Mac and Excel for the web, so use the Windows desktop editor if the named ribbon commands are unavailable in your version.
Load the data into Power Query
If your data is already an Excel table, click a cell in it and choose Data > From Table/Range. If the data is a normal range, Excel will prompt you to create a table before opening the editor. For a text or CSV file, choose the appropriate import option on the Data tab, such as Data > From Text/CSV, and select Transform Data to open Power Query Editor.
In the editor, check that the column containing the strings has the expected data type. Text extraction is most straightforward when the source column is typed as Text. If Power Query has interpreted an identifier as a number or date, change its type using the data type control in the column header or the Transform tab. Identifiers such as postal codes and product codes are usually better kept as text, even when they contain only digits.
Before making a transformation, look at several representative rows. A delimiter that appears in every value may be a reliable boundary; one that appears only sometimes may need special handling. Pay attention to blank values, extra spaces, and variations in capitalization or punctuation.
Extract text using the ribbon
For common patterns, use a built-in command rather than writing M yourself. Select the text column, then look under Transform > Extract. Depending on the task, choose an option such as Text Before Delimiter, Text After Delimiter, Text Between Delimiters, First Characters, or Last Characters. The dialog lets you supply a delimiter or character count where needed.
For example, suppose a FileName column contains Report.xlsx. Choose Text Before Delimiter and enter a period to return Report. If a Code column contains INV-4821, Text After Delimiter with a hyphen returns 4821. The preview updates before you commit the step, so check that it gives the intended result for more than one row.
By default, an extraction step usually replaces the selected column. If you need to preserve the original string, use Add Column rather than Transform, and choose the corresponding extraction command there if it is available in your editor. Another option is to create a custom column. Keeping the source column is helpful when checking results or when later steps still need the full value.
Use M functions for precise extraction
Power Query transformations are expressed in M, its formula language. To write an expression, choose Add Column > Custom Column, enter a new column name, and type the expression in the formula box. In these examples, each expression assumes the named source column contains text. The square-bracket reference, such as [Code], means the value in that column for the current row.
- Before a delimiter: Assuming the source column is named FileName and contains
Report.xlsx, enterText.BeforeDelimiter([FileName], "."). The result isReport. This returns the text before the first period, so a value such asannual.report.xlsxreturnsannual, notannual.report. - After a delimiter: Assuming Code contains
INV-4821, enterText.AfterDelimiter([Code], "-"). The result is4821. With more than one hyphen, this basic expression uses the first matching delimiter. - Between two markers: Assuming Message contains
Order [A-104] shipped, enterText.BetweenDelimiters([Message], "[", "]"). The result isA-104. - At a fixed position: Assuming Item contains
ID-4821, enterText.Range([Item], 3, 4). M counts positions from zero, so position 3 is the first character of4821; the result is4821. - Keep only digits: Assuming Phone contains
(212) 555-0198, enterText.Select([Phone], {"0","1","2","3","4","5","6","7","8","9"}). The result is2125550198.
These expressions are M, not worksheet formulas: they belong in Power Query Editor, not in a cell. If a function name or column reference is underlined or returns an error, check the spelling and confirm that the column name matches exactly. Column names with spaces can still be referenced inside the square brackets, for example [Order Code].
Choose delimiters, positions, or character rules
A delimiter-based method is usually easiest when the string has a clear marker, such as a comma, slash, colon, or bracket. It adapts to values of different lengths: extracting after a colon can work whether the text before it is short or long. However, it can produce an unexpected result if the marker is missing, repeated, or used for another purpose in some rows.
Fixed-position extraction is appropriate when the format guarantees that the desired text always starts and ends at the same character positions. For instance, a product code with a fixed prefix and a four-character suffix can be handled with Text.Range or the First Characters and Last Characters commands. It is fragile when a prefix changes length or optional spaces appear, because the same positions may then point to different content.
Character selection is useful when the goal is to normalize a string rather than take one continuous section. Text.Select can retain a defined set of characters; the example above strips formatting from a telephone number. A related function, Text.Remove, removes a specified set of characters instead. Neither function understands meaning: retaining digits will also retain unrelated numbers if they occur in the same string.
When a string has multiple separators, first decide which occurrence defines the boundary. For example, extracting an extension from a filename with several periods is not the same as extracting everything before the first period. Review the ribbon dialog’s options or use a custom expression suited to the pattern, then test it against values with repeated delimiters before relying on it for a full dataset.
Split a column instead of extracting one result
If you need several pieces from the same string, splitting may be more convenient than creating separate extraction steps. Select the column and choose Transform > Split Column > By Delimiter. You can select a delimiter and choose how to split, such as at each occurrence or at a specified occurrence, depending on the dialog options shown in your version. The result is multiple columns that can be renamed and typed separately.
For example, splitting West-4821-Open at each hyphen produces three fields: West, 4821, and Open. This is useful when every value follows the same structure and you want all components. If you only need the middle component, splitting still works, but a targeted extraction may create a simpler query. Check what happens when a row has too few separators; it may produce a null or a different layout than expected.
Text.Split is also available in M when a custom expression is preferable, but its result is a list rather than a plain text value. A user who is new to Power Query will often find the Split Column command easier to inspect and maintain than navigating list indexing in an expression.
Handle blanks, missing markers, and unexpected results
A common problem is a source value that does not contain the expected delimiter. The query may return an error for an extraction step that assumes the delimiter exists. In the preview, identify which rows fail and whether they are truly invalid or simply follow another format. Do not automatically replace every error with blank text until you know what the error represents; doing so can hide a data-quality issue.
Null and empty text are different. A null means there is no value, while an empty string contains zero characters. Either can appear in imported data. If the source is inconsistent, use conditional logic in a custom column to check for null or for the presence of a marker before extracting. For example, an expression can use an if condition and Text.Contains to return null for rows without a hyphen and extract only where the delimiter exists. Choose a fallback that makes sense for the dataset rather than assuming every missing delimiter means the same thing.
Whitespace is another frequent source of confusion. A result may look blank but contain spaces. Power Query’s Text.Trim removes leading and trailing whitespace from text; it does not extract a segment or remove spaces in the middle. You can apply Trim from the formatting commands on the Transform tab or use the function in a custom column. Also inspect the source for nonbreaking spaces or other characters that may not be visible in the preview.
If a step returns the wrong portion, check whether it is using the first occurrence of a delimiter while you intended the last, and whether the preview contains exceptions. Then inspect Applied Steps to see whether an earlier type change or transformation affected the source. You can select a step to view its output, edit its settings, or remove it. This makes it easier to locate where a result first diverges from expectations.
Refresh the query and preserve the source
When the extraction is correct, choose Home > Close & Load to return the result to Excel. Excel loads the transformed output as a table by default in common workflows. Later, use Data > Refresh All or refresh the query’s output table to rerun the transformation against its source. The sequence of transformations appears in Applied Steps and is reapplied during refresh; it is not a one-time edit to the original file.
Keep in mind that refresh depends on the source being available and on the query still being able to locate it. If a file is moved, renamed, or no longer accessible, the extraction formula itself may be fine while the query fails to refresh. For recurring work, keep a stable source location and check the loaded output after refresh, especially when incoming strings may have changed format.
Frequently Asked Questions
Can Power Query extract text between two characters?
Yes. Use the ribbon’s Text Between Delimiters command or create a custom column with Text.BetweenDelimiters. For example, with Message containing Ref: [Q7], Text.BetweenDelimiters([Message], "[", "]") returns Q7.
How do I extract the last part after a hyphen?
If each value has only one hyphen, Text.AfterDelimiter returns the part after it. If there are several hyphens and you need the text after the final one, do not assume the basic expression selects the last occurrence; configure the delimiter occurrence appropriately in the interface or build and test an expression for the actual pattern.
Can I keep the original text column?
Yes. Use an Add Column command or Add Column > Custom Column to create a separate result. A Transform command generally changes the selected column, so use it when replacing the source is intended.
Why does my extraction return an error for some rows?
Those rows may be null, blank, missing the delimiter, or formatted differently. Inspect the failing values and add a condition or correct the source pattern before deciding how to handle them. Replacing errors indiscriminately can conceal inconsistent data.
Does Power Query change the original source file?
No. A query applies steps while importing or transforming data and loads the result to Excel or another destination. It does not ordinarily edit the source file in place. Refreshing reruns the query, so the loaded output can change when the source changes.
Related
- Excel Conditional Formatting Question Mark Explained
- Excel Conditional Formatting Greyed Out? Fixes to Try
- How to Add a Dropdown List in Excel for Data Entry
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Remove Duplicates in Google Sheets: A Safe GuideTo remove duplicate rows in Google Sheets, select your data, then choose Data > Data cleanup > Remove…
- Protect a Sheet in Excel From Viewing: What Actually WorksTo keep a worksheet out of casual view in Excel for Microsoft 365 on Windows, hide its tab,…
- Excel Scatter Plot: How to Remove Values at the OriginTo remove only the point where both coordinates are zero from an Excel scatter plot, make helper columns…
- Google Sheets IFERROR: How It Works and When to Use ItIn Google Sheets, use IFERROR to show a useful alternative whenever a formula returns an error. For example,…
- Google Docs Employee Schedule Template: Create and Share ItYes. You can make a Google Docs employee schedule template by inserting a table, adding dates and shift…
- Excel Consolidate Rows With the Same ValueYes. To consolidate rows that share a value in Excel, summarize them with a PivotTable or Power Query’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.