Downloads
Power Query MSplitters and combinersIntermediate

Replacer.ReplaceText

Replaces part of a text value, every place where the old text appears, with the new text.

Syntax
Replacer.ReplaceText(text, old, new)

Returns: The changed text.

What it does

Replacer.ReplaceText changes text inside a value. Where Replacer.ReplaceValue swaps a whole value, this one swaps a piece of it, such as removing a dollar sign or a thousands separator from every amount in a column. It is most often passed to Table.ReplaceValue or List.ReplaceValue, which call it for each value.

Syntax and arguments

Example

Amounts were exported with a dollar sign and thousands separators, such as "$1,250.00". The team wants clean text that can be converted to numbers.

let
    Amounts = {"$1,250.00", "$480.50", "$75.00"},
    NoDollar = List.ReplaceValue(Amounts, "$", "", Replacer.ReplaceText),
    NoComma = List.ReplaceValue(NoDollar, ",", "", Replacer.ReplaceText),
    Numbers = List.Transform(NoComma, Number.FromText)
in
    Numbers

Numbers is 1250, 480.5 and 75. Two replacements are needed because each one removes one kind of mark. The conversion comes last, when the text is clean.

Example: a second, different use

Removing a prefix from account codes in a table column, using Table.ReplaceValue with Replacer.ReplaceText on the codes column.

Things to watch

In business terms

Stripping currency symbols, thousands separators and stray labels from exported amounts is one of the most common cleanup steps in finance data work. Keep each replacement visible, so a reviewer can see what was changed.

Related functions

All Power Query M functions · View as Markdown · M basics