To split a statement file that uses semicolons or pipes, select the column of raw lines, choose Data > Text to Columns, pick Delimited, and tick Semicolon or type the pipe character in the Other box. For a file you will receive every month, load it through Data > From Text/CSV and Power Query so the same split repeats on refresh.
What does the file look like inside?
Open the downloaded .txt or .csv in Notepad before doing anything. Say a line reads:
- 03-Apr-2026|UPI/GROCER; PUNE/5521|1200.00|40350.00
The pipe separates four fields: date, narration, amount and balance. The semicolon inside the narration is part of the text, so if you split on both characters you will cut the narration in two. Always split on the one character that appears the same number of times in every line.
How does Text to Columns handle a custom delimiter?
- Open the file in Excel (a .txt file starts a Text Import wizard), or paste the lines into column A.
- Select column A and choose Data > Text to Columns, then Delimited, then Next.
- Untick Tab and Comma. Tick Semicolon, or tick Other and type the pipe character, which sits above the backslash key on most keyboards.
- Watch the Data preview. Four neat columns means the delimiter is right.
- On the last step, set the date and reference columns to Text, choose a Destination so you keep the raw column, and click Finish.
If a count of the fields differs between lines, the narration probably contains your delimiter. Fix the odd lines first by hand, or pick a delimiter that cannot occur in the text.
Why is Power Query better for a monthly file?
Text to Columns is a one-off command. You repeat every click for next month's file, and you may forget a step. Power Query records the steps and replays them.
- Choose Data > Get Data > From File > From Text/CSV and select the file.
- In the preview, open the Delimiter list and choose Semicolon, or choose Custom and type the pipe character.
- Set Data Type Detection to Do not detect data types, then click Transform Data.
- Set the date column's type using Transform > Data Type > Using Locale with English (India), keep reference columns as Text, and set amounts to Decimal Number.
- Choose Home > Close & Load.
Next month, save the new download over the same file name and click Data > Refresh All. The split, the types and any cleaning steps run again without any clicks.
What are the common stumbles?
| Problem | Likely cause | Fix |
|---|---|---|
| Everything stays in one column | Wrong delimiter ticked | Open the file in Notepad and see which character separates the fields |
| Dates look like numbers | Date column was left on General | Set it to Date, using the English (India) locale in Power Query |
| Amounts will not add up | Numbers are stored as text | Select the column, use Text to Columns once and finish with General, or set Decimal Number |
| The last column has a stray character | Trailing delimiter on each line | Delete the empty column after splitting |
If the bank changes its delimiter one month, you do not need to rebuild the query. In the Query Editor, find Applied Steps on the right, click the gear icon beside the Source step, and change the Delimiter. The later steps stay as they were.
Some files arrive with no header row. In Power Query, double-click each Column1, Column2 heading and rename them Date, Narration, Amount and Balance. Use Transform > Use First Row as Headers only when row 1 really is the header.
Tidy up after splitting
Add a header row if the file has none, delete any title or summary lines above the first transaction, and check the row count against the statement. Save the cleaned version as an XLSX and keep the original text file somewhere you will find it later.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

