Automation Part 4: CLEAR(), FILL(), and SUBSTITUTE()

Overview

So, if you’ve followed along with the last three parts of this series, you’ve learned a lot! Automating data insertion into tables, automatically executing functions, and inserted data from one table into another. But we’re not done yet! Dashdash has a few more tricks to show you to get the most out of your spreadsheets. This time round, we’re going to cover CLEAR(), FILL(), and SUBSTITUTE() :slight_smile:.

Why do we use them?

When you want to… Use…
Clear a row of all its data (including functions). CLEAR()
Fill a range of cells with some data or a formula. FILL()
Replace a string in a text with another string. SUBSTITUTE()

CLEAR()

CLEAR()is a great little function for when you want to delete a singular row of all its data - including the formulas.

Syntax

CLEAR(row)

Parameter Mandatory Description
row Yes The row that you want to clear all data from, including the formulas.

Examples

To simply clear row 3 of all its contents:

=CLEAR(3)

FILL()

FILL() lets you take a cell or range, and then copy it down a specified number of rows. So instead of a lot cell dragging, you can just specify that you want it copied down 20 rows, and voila!

Syntax

FILL(range, rows, [overwrite])

Parameter Mandatory Description
range Yes The cell or range you want to copy down. For example: A2.
rows Yes The number of rows to extend the cell or range by. For example: 20.
overwrite No The overwrite rule when copying cells. Choose either:
-0 to write only in empty cells.
- 1 to overwrite and recompute only if the cell contents are different (default).
- 2 to overwrite anything in the cells, regardless of the content already in them.

Examples

To fill 20 rows with the contents in A1:

=FILL(A1, 20)

To have an ascending list of numbers filled down 100rows (given you have 1 cell A1 and =A1+1 in cell A2):

=FILL(A2, 100)

To fill 10 rows with the contents of A1:B1, but only if they’re empty:

=FILL(A1:B1, 10, 0)

SUBSTITUTE()

SUBSTITUTE() is used to replace one string in a text with another. Other than the usual find and replace in normal text, you can use it to modify JSON data objects so that they contain exactly what you need. You’ll see this one used a lot in our templates, as it really does make your life simpler.

Syntax

SUBSTITUTE(original_text, search_for, replace_with)

Parameter Mandatory Description
original_text Yes The original text that you want to change. For example "Dashdash is amazing" or A2.
search_for Yes The string that you want to change. For example "amazing".
replace_with Yes The string that you want to replace the search_for string with. For example: "brilliant".

Examples

To modify the text With love, from Dom. to With love, from dashdash.:

=SUBSTITUTE("With love, from Dom.", "Dom", "dashdash")

To modify the text in cell A2 and replace any occurrence of the string in A3 with easy like a Sunday morning.:

=SUBSTITUTE(A2, A3, "easy like a Sunday morning.")

To add an additional key-value pair of "domain":"dashdash.com"to this JSON object {“companyName”:”dashdash”}:

=SUBSTITUTE({“companyName”:”dashdash,"}",', "domain":"dashdash.com" } ')

:bulb: Make sure to check out our How to Modify JSON objects in dashdash forum post for more examples!

Challenge

And like always, let’s finish this one off with a round of challenges!

  1. Try to conditionally clear the contents of some rows. Hint: You might need to use the MATCH()function.
  2. Try to conditionally fill a range of cells with the NOW()function, depending on the contents of another cell.
  3. In the I/O/U app from the previous article, use SUBSTITUTE()to display the age for each row. Hint: Use the YEAR() and TODAY()functions to make life a little easier for you :slight_smile:

Summary

Right, so that’s the end of this tutorial series on Automation in dashdash :innocent:. You’ve learned a lot in this series: how to interact with data, automate cell and formula execution, insert data from one table to another, clear as well as fill rows, and change the contents of a cell or string with something else :muscle: . We do hope it helped to clear up a few things for you and that’ll supercharge your spreadsheets creation! Now, it’s time to get building and how us what you got :rocket:!

Next month in our tutorial series, we’ll focus a bit on how to make your spreadsheets interactive :slight_smile: Can wait to show you just what you can do!

2 Likes