Text.Insert
Inserts a piece of text into a value at a zero-based position.
Text.Insert(text as nullable text, offset as number, newText as text) as nullable textReturns: The new text with the insertion made, or null when the input is null.
What it does
Text.Insert adds text at a given position without removing anything. The main finance use is reformatting a code. A date stored as "20261029" can be given separators to read "2026-10-29", and a bank reference can be given a dash at the position the bank expects.
Syntax and arguments
- text: the value to change.
- offset: the zero-based position where the new text goes. Position 4 is after the fourth character.
- newText: the text to insert.
Example
A bank feed stores dates as eight digits, such as "20261029". The team inserts the separators so the text reads like a date, then converts it to a real date.
let
Raw = "20261029",
WithDash = Text.Insert(Text.Insert(Raw, 4, "-"), 7, "-"),
AsDate = Date.FromText(WithDash)
in
{WithDash, AsDate}
WithDash is "2026-10-29". The first insert puts a dash after position four, and the second insert after the new position seven. Date.FromText then reads the ISO form, so the result is the date October 29, 2026.
Example: a second, different use
Insert a dash into a fixed length account code such as "100040" to show it as "1000-40" for a report that uses a split format.
Things to watch
- Positions are zero-based and refer to the value as it is at that step. After one insert, the positions after it move.
- An offset past the end of the text adds the new text at the end.
- The result is text. Convert it to a date or a number when you need arithmetic.
- Null in gives null out.
In business terms
Source systems often store dates and codes without separators. Inserting them is a simple way to make the values readable, or to fit a format a bank or tax portal expects.