This is not a post about formulas or structures to create a successful story. You can read about those in my story structure series of posts.

This post is the second on how I use functions in Google Sheets, specifically my story log workbook. The last post focused on creating various metrics using functions.

Since my stories are paragraph length and I have several stories collected electronically so far, I decided that I wanted to build in the ability to display stories with certain words. For example, display stories that include “Dad” and “car.” Before I share my final functions and formulas, I’ll provide a basic intro to managing columns of information.

Since I use my iPad most of the time, all of these examples are using Google Sheets on the iPad. The browser-based version has more functionality—and these options may be in different places—but everything on the iPad is available in the browser version.

Column sort

Once you have several columns of data, the lowest level way to find something easily is a simple column sort. Select a column and choose “Sort A-Z” or “Sort Z-A” to sort your columns.

All of these examples are using paragraphs from The Tale of Peter Rabbit. It has 34 paragraphs. I added a paragraph number to each row and also added a word count for each paragraph.

The formula for the word count is below. It counts the number of characters in the paragraph (including spaces) minus the number of characters (excluding spaces) plus 1.

=LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))+1

For cells with numbers or single words, this can be a way to quickly find something. However, this won’t work if a cell contains multiple words. The sort uses the first word or number in the column to be sorted.

Filter in place

Filtering in place is a bit more powerful than the column search, but again is limited to the first word or number in a cell. However, you can filter using multiple columns. I maintain a list of all of my sermon notes, and I use a filter at the top of the list to quickly find a sermon or speaker by selecting options from multiple columns.

This type of column-based filtering still does not make it easy to return a list of when searching several words in a cell.

Basic filter function

By creating a new worksheet (within the same workbook) and writing a filter formula, you can easily bring back a list of rows that meet the determined criteria.

In this example I created a simple formula returning the paragraph number and the paragraph (Columns A and B) for a selected paragraph number.

This formula, written in Cell A4 of my filter worksheet, looks at the data list (PeterRabbit) and returns the value in Column A (Paragraph Number) and Column B (Paragraph) when the value in Column A equals the search criteria, Cell B1 of the filter worksheet.

=filter(PeterRabbit!A:B,PeterRabbit!A:A=B1)

As long as the value used for the criteria is part of the data set everything works well. However, when I select a paragraph number beyond 34 (the last paragraph of Peter Rabbit), the formula results in an error, designated as “#N/A” in Cell A4.

Basic filter function with error message

“#N/A” tells me that an error occurred, but doesn’t explain why. By adding an “if” statement to the formula, I can generate a custom message when the search criteria values are exceeded.

If Cell B1 (my criteria to filter by) exceeds the maximum paragraph number in Peter Rabbit worksheet Column A, then the message should be displayed, “The last paragraph is” followed by the maximum paragraph number. If Cell B1’s value is contained within the set of paragraph numbers, then the paragraph number and paragraph are returned.

=if(B1>max(PeterRabbit!A:A),"The last paragraph is "&max(PeterRabbit!A:A),filter(PeterRabbit!A:B,PeterRabbit!A:A=B1))

Filter using a word or phrase within a paragraph

Now we come to the real functionality I need: returning a list of paragraphs based on a word or phrase contained anywhere in the paragraph.

This formula uses the search function in combination with filter to return only the paragraphs (Column B in the data set) where the criteria word or phrase is contained. For this example, I searched for “rabbit,” and 5 paragraphs were displayed.

=filter(PeterRabbit!B2:B,search(B1,PeterRabbit!B2:B))

Filter using 2 phrases within a paragraph

In this formula multiple search criteria are used, both word searches within paragraphs. A list of paragraphs is returned if both words are contained in the paragraph. If Phrase 1 or Phrase 2 is empty, paragraphs will be returned if the other phrase exists within the paragraph.

=filter(PeterRabbit!B2:B,search(B1,PeterRabbit!B2:B), search(B2,PeterRabbit!B2:B))

If a search word or phrase is entered that is not contained in the data set, then an error will be displayed.

Filter with an error message

This final formula takes the same 2-word search capability above and adds an error message using “IfError.” IfError says, “if there’s an error, display a customized message, otherwise display what is requested successfully by the formula.“

In this example, “Jkjkj” is not included in the story of Peter Rabbit. Above, the #N/A error was displayed. Below, my message, “Word(s) not included” is displayed.

=iferror(filter(PeterRabbit!B2:B,search(B1,PeterRabbit!B2:B), search(B2,PeterRabbit!B2:B)),"Word(s) not included.")

Final Thoughts

If I continue collecting a story per day for even just a few more years, I will have over 1,000 stories. Without any type of search functionality, I would have to read the entire list every time to find a story. Using the filter-based search I’ve described here, I’ll be able to quickly find stories. It’s really a Google search specifically for my story log.

Functions Used

• Len—Returns the number of characters in a cell.
• Substitute—Substitutes one designated text for another.
• Filter—Generates a specified number of rows based on a criteria.
• If—Essential function that evaluates a condition.
• Max—Displays the maximum value in a selected data set.
• Trim—Removes the spaces at the beginning and end of a selected cell.
• Search—Used to find text within other text.
• IfError—Displays a custom message if an error occurs.