How to Split Text by Position in Power Query

by Excel and Adam | Nov 17, 2025 | Excel Power Query

Split­ting text by length in Pow­er Query can trans­form clut­tered data into orga­nized, usable infor­ma­tion with­out com­plex for­mu­las. This tech­nique allows you to break down text strings into sep­a­rate columns based on spe­cif­ic char­ac­ter posi­tions, mak­ing it espe­cial­ly use­ful when deal­ing with fixed-width data for­mats that con­tain mul­ti­ple pieces of information.

The real pow­er of this approach lies in its abil­i­ty to han­dle irreg­u­lar split­ting require­ments where each sec­tion has a dif­fer­ent length.

In my video, I demon­strate the com­plete process of split­ting text by length in Pow­er Query:

Understanding the Data Structure

When work­ing with text data that needs to be split, it’s essen­tial to first ana­lyze the struc­ture of your text. In the demon­stra­tion, I work with a dataset where each text string con­tains sev­er­al pieces of infor­ma­tion with vary­ing lengths:

• Per­son infor­ma­tion (30 characters)
• Delim­iter char­ac­ters (semi­colons, pipes) that need to be removed
• Date infor­ma­tion (appear­ing as numbers)
• Cur­ren­cy val­ues in dif­fer­ent formats

The chal­lenge lies in the fact that each sec­tion has a dif­fer­ent length, mak­ing stan­dard split func­tions less effec­tive. This is pre­cise­ly where Pow­er Query’s split by posi­tion fea­ture becomes invaluable.

Importing Data to Power Query

The first step in the process is to import your data into Pow­er Query. This can be done eas­i­ly by select­ing your data table and using the From Table/Range option in the Data tab. Once your data is in Pow­er Query, you’ll have access to pow­er­ful trans­for­ma­tion tools that aren’t avail­able in stan­dard Excel.

Pow­er Query pro­vides a user-friend­ly inter­face where you can see your data and apply var­i­ous trans­for­ma­tions step by step. This visu­al approach makes it eas­i­er to track changes and ensure that your data is being processed correctly.

Splitting Text by Position

With the data import­ed into Pow­er Query, we can now split the text col­umn based on spe­cif­ic posi­tions. Here’s how to do it:

1. Go to the Home tab in Pow­er Query
2. Select Split Col­umn and choose “By Posi­tions” (not “By Num­ber of Characters”)
3. Enter the spe­cif­ic posi­tions where you want to split the text

In the exam­ple, I need­ed to split at posi­tions 0, 30, 31, 39, and 41. It’s impor­tant to note that Pow­er Query counts from zero for the first posi­tion, not one. These num­bers rep­re­sent the start­ing points for each sec­tion of text.

After press­ing OK, Pow­er Query cre­ates new columns based on these posi­tion splits. The result is five sep­a­rate columns, each con­tain­ing a dis­tinct part of the orig­i­nal text string.

Refining the Split Data

Renaming and Removing Columns

After split­ting the text, we need to orga­nize our data by giv­ing mean­ing­ful names to the impor­tant columns and remov­ing unnec­es­sary ones. In the for­mu­la bar, I renamed the columns to reflect their content:

• “Text.1” became “Per­son”
• “Text.3” became “Date”
• “Text.5” became “Pay­out”

For columns con­tain­ing delim­iters or oth­er unwant­ed infor­ma­tion (in this case, “Text.2” and “Text.4”), we can sim­ply delete them by select­ing them with Ctrl+click and press­ing the Delete key.

Correcting Data Types

Once we have our prop­er­ly named columns, we need to ensure that each col­umn has the cor­rect data type. In the demonstration:

• The “Date” col­umn was ini­tial­ly rec­og­nized as an inte­ger and need­ed to be con­vert­ed to the date data type
• The “Pay­out” col­umn con­tained cur­ren­cy val­ues in dif­fer­ent for­mats that need­ed prop­er interpretation

Pow­er Query can auto­mat­i­cal­ly detect and con­vert data types, but some­times man­u­al inter­ven­tion is nec­es­sary. By click­ing on the data type icon in the col­umn head­er, you can force Pow­er Query to inter­pret the data as a spe­cif­ic type.

Working with Regional Settings

An impor­tant aspect of work­ing with dates and cur­ren­cy val­ues is under­stand­ing how region­al set­tings affect data inter­pre­ta­tion. In Pow­er Query, you can adjust these set­tings to match the for­mat of your data.

To access these set­tings, go to:

1. Options and Settings
2. Query Options
3. Cur­rent Workbook
4. Region­al Settings

In my demon­stra­tion, the region­al set­tings were set to “Eng­lish (Unit­ed King­dom)” which allowed Pow­er Query to cor­rect­ly inter­pret the pound (£) cur­ren­cy sym­bols regard­less of their posi­tion in the text. If your data uses dif­fer­ent region­al for­mats, you can adjust these set­tings to match your needs.

For indi­vid­ual columns, you can also click on the data type icon and select “Using Locale” to spec­i­fy both the data type and region­al for­mat for that par­tic­u­lar col­umn. This gives you fine-grained con­trol over how Pow­er Query inter­prets your data.

Finalizing the Transformation

After com­plet­ing all the nec­es­sary trans­for­ma­tions — split­ting the text, renam­ing columns, remov­ing unnec­es­sary columns, and set­ting the cor­rect data types — the final step is to load the trans­formed data back into Excel.

From the Home tab, select “Close & Load” to export your prop­er­ly orga­nized table back to Excel. The result is a clean, struc­tured dataset with sep­a­rate columns for per­son infor­ma­tion, dates, and pay­ment val­ues, all with the appro­pri­ate data types.

This tech­nique of split­ting text by posi­tion in Pow­er Query is par­tic­u­lar­ly use­ful when deal­ing with fixed-width data exports from lega­cy sys­tems, stan­dard­ized report out­puts, or any sit­u­a­tion where text strings con­tain mul­ti­ple data points at known posi­tions. By mas­ter­ing this approach, you can quick­ly trans­form dense, com­bined text fields into orga­nized and usable data.