Replace and Substitute

Explore

Gallery

Replace and Substitute

⁠
⁠
⁠

Formula: Replace() and Substitute()

Category: String

⁠

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.

⁠

Substitute()

Checks if string of text ends with chosen values

Or put another way...

⁠

Replace() uses four parameters:

⁠

Text

Replace(Text, Start, NumberOfCharacters, ReplacementText)

This is the text you want to the formula to search.

⁠

Start

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The character position you want to start from.

⁠

NumberOfCharacters

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The number of characters you want to replace in total.

⁠

ReplacementText

Replace(Text, Start, NumberOfCharacters, ReplacementText)

The text you want to replace the deleted base text.

Substitute() uses three parameters:

⁠

Text

Substitute(Text, SearchFor, ReplacementText)

This is the text you want to search.

⁠

SearchFor

Substitute(Text, SearchFor, ReplacementText)

The text you want to search for.

⁠

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:

⁠

And make the year:

0000

⁠

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()

⁠

Substitute()

⁠

⁠ ⁠ ⁠ 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: https://coda.io/formulas#Replace ⁠ ⁠ 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()

⁠

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!

⁠