Yes. In Excel for Microsoft 365, open the Power Query Editor, select the column, then choose Home > Split Column > By Number of Characters to split text at a fixed character count. For several exact cut positions in one step, use Power Query’s M function Splitter.SplitTextByPositions.
What “split by position” means
A position-based split divides text according to where characters occur, rather than looking for a separator such as a comma, space, or hyphen. If each value has the same layout, this is useful for fixed-width data: for example, a seven-character code might contain a three-character region followed by a four-character identifier.
Suppose the Code column contains NYC4821. Splitting after three characters produces NYC and 4821. The split point is the boundary after character three; it is not a search for the letters or numbers themselves. Power Query preserves the characters on both sides and places the resulting pieces in separate columns.
This method works best when the data has a consistent structure. If the first part can have different lengths, a delimiter or a rule based on the content may be more appropriate. A position split will follow the same character count for every row, even when a value is unexpectedly short or formatted differently.
Split a column in the Power Query Editor
- Open the query in Power Query Editor. In Excel, you can use Data > Queries & Connections, then double-click the query, or right-click it and choose Edit.
- Select the column you want to divide. Check that it contains text in the expected layout; if necessary, set its type to Text using the data type control in the editor.
- Choose Home > Split Column > By Number of Characters. The same split options may also be available from the column’s context menu.
- Enter the number of characters for the split and choose how the split should be applied: once from the left, once from the right, or repeatedly. The available wording can vary slightly with the Power Query interface.
- Choose OK, inspect the new columns and their names, then select Home > Close & Load or Close & Load To to return the result to Excel.
For the example code NYC4821, enter 3 and split once from the left. The result is two columns, normally given generated names such as Code.1 and Code.2. Rename them to something meaningful, such as Region and Identifier. If you split from the right by three characters instead, you get NYC4 and 821; direction matters.
Choosing the split direction and repeat option
The character count and the direction answer different questions. The count identifies the size of a segment or the location of a cut; the direction determines which end Power Query starts from. Use a left split when the prefix has a known length, and a right split when the suffix has a known length. For example, if a value always ends in a two-character status code, splitting once from the right isolates that code even if the prefix varies in length.
- Once, from the left: make one cut after the specified number of characters, counting from the beginning.
- Once, from the right: make one cut at the specified distance from the end.
- Repeatedly: split at each repeated interval. For a ten-character value split every four characters, this can produce a four-character piece, another four-character piece, and a final remainder.
Before choosing Repeatedly, consider whether the values really are consistently grouped. If the final segment is shorter than the interval, it remains shorter; Power Query does not add padding to make it the same width. Preview several rows, including the shortest and longest values, rather than judging the split from the first row alone.
Split at several exact positions with M
The dialog’s number-of-characters option is convenient for a regular interval or a single cut. If the required cut points are different—for example, after characters three and six—use the M function Splitter.SplitTextByPositions. Positions count from the start of the text, with zero as the starting offset: a cut at position 3 comes after the first three characters.
With a source step named Source, a text column named Code, and desired cuts after characters three and six, a split step can look like this:
= Table.SplitColumn(
Source,
"Code",
Splitter.SplitTextByPositions({3, 6}, false),
{"Region", "Group", "Item"}
)
For a value such as ABCDEF789, those positions produce ABC, DEF, and 789. The positions are boundaries, not one-based character numbers to subtract from: {3, 6} means cut after three characters and then after six characters. The list of output names must match the number of resulting pieces. In the Power Query Editor, open Home > Advanced Editor to view or edit the query’s M code; a split created through the interface is often the safer starting point if you are unfamiliar with M.
Fixed-width files and practical examples
Position splitting is common when importing fixed-width reports or codes whose fields have prescribed lengths. Imagine a record consisting of a four-character department, a two-character year, and a five-character sequence number. Cuts after positions four and six separate those three fields. A run of spaces between fields is not needed because the field boundaries are defined by position.
It can also help when a product code has a stable prefix, or when a date arrives in a compact format. For example, a text value like 20261103 can be split after positions four and six into year, month, and day components. This creates text columns; it does not automatically guarantee that the pieces are valid dates or numbers. After splitting, set appropriate data types and check that the source uses the expected order.
For a true fixed-width text file, Power Query may offer a more suitable import workflow than splitting one already-loaded column. Import the file through Excel’s text/CSV data tools and review the available import options. The best route depends on how the file is structured and which import experience is available in your Excel build. Regardless of the route, verify the column boundaries against the source specification rather than assuming that spaces or visual alignment define them.
Common problems and how to avoid them
- Unexpected spaces: leading spaces count as characters, so a visually similar value may split differently. Use a trim or clean step when appropriate, but check whether spaces are meaningful before removing them.
- Short or blank values: a row with fewer characters than the requested position may yield an empty or shorter piece. Inspect those rows and decide whether to keep them, correct them, or flag them as exceptions.
- Mixed formats: if some codes contain an extra prefix or optional character, fixed positions can shift every later field. Identify and handle the alternate format separately instead of assuming one cut fits all.
- Automatic type detection: codes that look numeric may be converted to numbers, which can remove leading zeros. Set identifier columns to Text before relying on their fixed character positions.
- Too many pieces: repeatedly splitting can create more columns than intended when values are longer than expected. If the specification requires a fixed number of fields, review the longest records and consider explicit cut positions.
Power Query records each operation as an applied step. If a split gives the wrong result, select or remove the relevant step in the Applied Steps pane, then redo it with the correct position or direction. Fixing the step is generally preferable to editing the loaded worksheet cells, because a refresh can replace those cells with the query output.
Position splits versus delimiter splits
Choose a position split when boundaries are fixed by character count. Choose Split Column > By Delimiter when a reliable separator marks the boundary. For example, NYC-4821 is often better split at the hyphen than after a fixed number of characters, because the delimiter expresses the structure directly.
A delimiter split is more tolerant of changing field lengths, but it depends on the separator being present and unambiguous. A position split does not need a separator, but it depends on consistent widths. If neither condition holds, use a transformation based on the actual business rule, such as extracting text before a known marker or applying conditional logic. The question is not which method is universally better; it is which one matches how the source data is defined.
Refresh behavior and platform notes
Once loaded, a position split is part of the query and is reapplied when the query refreshes. New rows from the source are split using the same rule. That makes the transformation repeatable, but it also means a change in the source layout can cause errors or misleading output without changing the query itself. Keep a few representative records in your checks, especially after the source system changes.
These steps describe Excel for Microsoft 365 on Windows. Power Query is also available in Microsoft 365 for Mac, but the available connectors and interface details can differ by Excel version. Excel for the web does not provide the same full Power Query authoring experience as the Windows desktop application, so use the desktop editor when you need to create or adjust this split.
Frequently Asked Questions
Can Power Query split at different character positions?
Yes. The interface’s By Number of Characters command is suited to a count or repeated interval. For distinct cut points in one operation, use Splitter.SplitTextByPositions in M, with a list such as {3, 6}.
Are Power Query positions zero-based?
For Splitter.SplitTextByPositions, positions are offsets from the beginning of the text. A position of 3 makes a boundary after the first three characters. This can be confusing if you are thinking of the third character itself rather than the boundary after it.
Can I split from the end of the text?
Yes. In the By Number of Characters dialog, choose the once-from-the-right option when the suffix has a known length. If you need several custom positions or more complex behavior from the end, write and verify an M transformation that matches the data rule.
Will a position split preserve leading zeros?
It can, provided the source is treated as text. Set code-like values to Text before splitting and keep identifier pieces as Text after the split. If a value has already been converted to a number, zeros removed earlier cannot be recovered just by dividing the remaining digits.
Does splitting change the original source file?
No. Power Query creates a transformed query result; it does not edit the original source file. The result in Excel can be refreshed from that source, and the recorded split step is applied again during refresh.
Related
- X++ Enum Values in SQL: How Stored Numbers Work in D365
- How to Add Speaker Notes to Your PowerPoint Slides
- What Is Balance in Accounting? Meaning and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- General in Excel: Find and Use the Number Format SettingIn Excel for Microsoft 365 on Windows, find General on the Home tab, in the Number group: select…
- Create Water Bottle Labels in Word: A Practical GuideTo create water bottle labels in Microsoft Word, choose a label size, set up the page or label…
- How to Create a Style Template in Word and Reuse ItTo create a reusable style template in Word, format the document’s styles, then save the file as a…
- JavaScript split() Explained: Strings, Limits, and ExamplesIn JavaScript, split() is a string method that divides a string wherever a specified separator occurs and returns…
- How to Schedule Send an Email in New Outlook for LaterTo schedule an email in New Outlook, compose your message, open the menu beside Send, choose Schedule send,…
- DAX Measure Switch Based on a Column Value in Power BIYes. In a DAX measure, use SWITCH to return a different measure for each value, and use SELECTEDVALUE…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.