---
title: "Use the SUBSTITUTE Function to Replace Existing Text with New Text. "
slug: "substitute-function"
description: "Learn to use SUBSTITUTE to replace one or all instances of a text string with a new value. "
tags: ["formula syntax", "functions ", "Modeling and Formulas", "Pigment Blocks", "Pigment Boards", "SUBSTITUTE", "Text Replacement"]
updated: 2025-05-30T09:42:26Z
published: 2025-08-22T10:57:28Z
canonical: "kb.pigment.com/substitute-function"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SUBSTITUTE function

## ​​Description

Substitutes one or all occurrences of a Text within a Text with a new Text.

## Syntax

`SUBSTITUTE(Text to Search, Text to Replace, Text to Replace With, [, Occurrence Number to Replace])`

## Arguments

| Argument | Type | Dimensions | Description |
| --- | --- | --- | --- |
| *Text to Search* (required) | Text | Any Dimensions | Text to search in. |
| *Text to Replace* (required) | Text | No Dimension or Dimensions of *Text to Search* | Text within *Text to Search* to replace. |
| *Text to Replace With* (required) | Text | No Dimension or Dimensions of *Text to Search* | Text to substitute all found occurrences of *Text to Replace* with. |
| *Occurrence Number to Replace* (optional) | Integer | No Dimension or Dimensions of *Text to Search* | Number of the occurrence found to substitute. By default, all found occurrences are replaced. If specified, only the given occurrence is replaced. |

## Returns

| Type | Dimensions |
| --- | --- |
| Text | Dimensions of *Text to Search* |

The original *Text to Search* with all *Text to Replace* substituted by *Text to Replace With.*

`BLANK` if *Occurrence Number to Replace* is a negative integer.

## Examples

| Formula | Result | Description |
| --- | --- | --- |
| `SUBSTITUTE(“aaa”, “a”, “b”)` | “bbb” | All occurrences of “a” are replaced with “b”. |
| `SUBSTITUTE(“aaa”, “a”, “b”, 2)` | “aba” | Only the second occurrence of “a” is replaced by “b”. |
| `SUBSTITUTE(“Fimamcial Analyst” “Fimamcial”, “Financial”)` | “Financial Analyst" | The Text “Fimamcial” is replaced with “Financial” |
| `SUBSTITUTE(“abc”, “A", “d”)` | “abc” | “A” is not present in “abc”. Nothing is replaced. |
| `SUBSTITUTE(“abc”, “a”, “e”, -1)` | `BLANK` | The *Occurrence Number to Replace*, -1, is invalid. |

## See also

Excel: [SUBSTITUTE](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)

Related articles: [FIND](/v1/docs/find-function), [CONTAINS](/v1/docs/contains-function)

> [!TIP]
> More of a hands-on learner?
> 
> Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace. It includes examples of every formula and modifier in Pigment!

<style> p[data-block-id] {font-size:1rem;} ul li p[data-block-id] {margin-bottom: 0;} ul[data-type="taskList"] li div p[data-block-id] {margin-bottom: 0;} ol li p[data-block-id] {margin-bottom: 0;} table tbody th p[data-block-id] { margin-bottom: 0;} blockquote p[data-block-id] {margin-bottom: 0 !important;} &nbsp;p[data-block-id]:empty::after {content: "\00A0";} </style>