Replace and Substitute
Share
Explore

icon picker
Replace and Substitute

Formula: Replace() and Substitute()
Category: String
replace.svg
Replace()
Replace text within a numbered range
It’s kind of like saying...
Hey Coda, within this block of text, take out the letters/numbers between these two characters and replace it with this.
player-change.svg
Substitute()
Checks if string of text ends with chosen values
Or put another way...

Replace() uses four parameters:
text-color (5).svg
Text

Replace(Text, Start, NumberOfCharacters, ReplacementText)

This is the text you want to the formula to search.
play (2).svg
Start

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The character position you want to start from.
100 (1).svg
NumberOfCharacters

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The number of characters you want to replace in total.
find-and-replace (1).svg
ReplacementText

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The text you want to replace the deleted base text.

Substitute() uses three parameters:
text-color (6).svg
Text

Substitute(Text, SearchFor, ReplacementText)

This is the text you want to search.
search (2).svg
SearchFor

Substitute(Text, SearchFor, ReplacementText)

The text you want to search for.

find-and-replace (2).svg
ReplacementText

Substitute(Text, SearchFor, ReplacementText)

The text you want to substitute.
Whenever you work with data and databases, you will find yourself needing to modify text values. Why? If you haven’t run into them already, here are a couple of examples:
Standardize inputs from various sources into a standard format.
Change specific niche characters into more recognizable ones.
Delete information from a string.
Replace and Substitute are both great for these. Before we get into some examples though, lets just see how they work:
Original Text:
This is a very simple sentence written on Sep. 29th, 2022.
Replace the Day of the month as:
00
0
And make the year:
0000
0
Modified Text using Replace():
This is a very simple sentence written on Sep. 0th, 0.
Modified Text using Substitute() :
This is a very simple sentence written on Sep. 0th, 0.
Right click into each of the formulas above. Which one seems simpler?
Replace()
1
Substitute()

Confetti.gif
You got it!
The Substitute() formula is a lot simpler. This is because Replace() requires you to determine what will be replaced in your text by first defining a position (by number). So you have to do some extra formula work to find the position of your desired text and its length using Find()
Lets look at one practical use-case to learn a little more...
This is a body of the text that will help us learn about Replace() and Substitute(). Those are 2 different formulas that allow us to modify text so that it falls into a standard format.
There will be many examples to show and you better pay attention, because these are 2 awesome formulas.
If you need more information on them you can always find the here:
Hopefully that helps!

Replace the Digit 2 with the word “two”

Type the word “two” in the box to the right to replace it in the text.

Modified with Substitute()
This is a body of the text that will help us learn about Replace() and Substitute(). Those are different formulas that allow us to modify text so that it falls into a standard format. There will be many examples to show and you better pay attention, because these are 2 awesome formulas. If you need more information on them you can always find the here: https://coda.io/formulas#Replace Hopefully that helps!
Modified with Replace()
This is a body of the text that will help us learn about Replace() and Substitute(). Those are different formulas that allow us to modify text so that it falls into a standard format.
There will be many examples to show and you better pay attention, because these are 2 awesome formulas.
If you need more information on them you can always find the here:
Hopefully that helps!
Why didn’t it replace BOTH number 2’s?
Good! you noticed that Substitute() doesn’t replace all occurrences of a word/string, but only the first one. Look at the formula below and see how we can replace both of them.
This is a body of the text that will help us learn about Replace() and Substitute(). Those are different formulas that allow us to modify text so that it falls into a standard format. There will be many examples to show and you better pay attention, because these are awesome formulas. If you need more information on them you can always find the here: https://coda.io/formulas#Replace Hopefully that helps!

Done
Modify addresses to get them ready for a FedEx Shipment
Done
Standardize a group of imported HTML url’s
Done

Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.