How to Find and Replace in Coda.io ?
Use basic Coda functions
The best way to simulate a find & replace is using Regex.
This is not easy peasy, but it will likely fit your needs.
We developed a solution for enterprises to conserve the styling. This is an expensive solution for SMB maybe even for Enterprises. Due to the lack of proper native print to PDF functionalities, a lot of value evaporates.
The logic described in what follows is although correct, likely not satisfying your needs. Over time our insights developed.
You can easily use the Find and Replace features in any spreadsheet like Excel to search for a particular number or text string. You can either locate the search item for reference, or you can replace it with something else. Though it is simple, there are even videos explaining how to execute this simple task that only requests filling out two fields: find what you are looking for and the replacement.
Substitute
In a previous version of this blog I started with the function Find()followed by Replace(). I changed my mind after an exchange with Joostmineur and came to the conclusion that in most cases a Find and Replace in Coda is easily done with Substitute(). We first look into this function and we finish with the Find & Replace functions Coda offers, together with a Search Control that came out February 12, 2021 a really nice tool.
Below you see that Substitute() like Replace() is part of the same group of functions. We come back to this later, first how to apply substitute?
Substitute() can be used when you want to insert text or you want to refer to information available in other columns (lists) anywhere in your doc. Once you go for the hard coded text option between “ ”, you have the easy variation you are used to when using Find & Replace in any spreadsheet. This goes really fast and does the job.
Refering to any other column is easy as well and this is way more user friendly than in any spreadsheet. It is just linking the name of the column and off you go. Below I reference to the column ‘new name’ to get per row all the values I want to replace when in domain it finds “.be”.
To avoid misspelling based on caps and lowercase, you can add Lower() to both the column you reference to and in the formula you create.
Maybe you consider keeping the source value as superfluous and you want to replace directly in the column. In that case you add a button to each row and by pushing the buttons you modify values in the column of your choice in each row. You can also push all buttons directly with a single button using a RunActions()
Ýou get something as below. Please note that you have to carefully design the ModifyRow() function with a focus on ‘thisRow’
thisRow.ModifyRows(thisTable.domain,
thisRow.domain.Substitute(".be", thisRow.[new name]))The Substitute() function ís easy and goes fast, be it directly using a button or via creating a new list.
The Find Function in Coda
Coda offers also the function Find() to ‘find’ something. The result is not something that is highlighted on your page. Instead you get the first position of the string you are looking for in the row in the column you are referencing. That is visually speaking not so attractive, but very practical if you construct a formula. Below again an easy example, we try to find “.be” . If your search term is not found, Find() returns -1
In the above you see that at row 4 we find that at position 7 the “.be” starts. Sorting on the position gives you an overview of rows that match your search. However, there is a better way to have a visual appealing overview.
Creating a Control
The above sorting is not necessary if you are using a filter with a search bar. This is in many case practial for it permits you to ‘see’ what you have to replace. You only see the results that match your search. You get this result in two steps via Filter → Interactive Filter → Create control.
You can also set this Text input control to look in more than one column. Below how this works. You go to the filter right top, select the control you are working with and you adapt the function as below. This function I got from Joostmineur who wrote about this in this Coda Doc.
In short, the Find() function shows you the first position of the string you are looking for as a number. The second is a filter that provides a visually appealing oversight´, but does not help you to create a formula, it is eye candy.
To find & replace something you can use Find() instead of Substitute() but only if you combine it with Replace(). The function Replace() requires a bit of effort and is composed out of three elements.
- The start of your replacement
- How many characters you want to replace
- The replacementText
One of the many great things in Coda is that each element in a function can be or a fixed item like a number / a text or another function. When using Replace() you might want to go for a more functional and thus dynamic approach. The Coda documentation states: “Replace a range within text”. This is a more formal approach of replacing something, it is based on numbers and thus on positions. The function Substitute() did not care about positions, it simply looked for a matching value . Replace() is different also in the sense that it asks for the start of your replacement, that can be a single number or when a FormulaMap() is put at work, multiple numbers, multiple positions. So replace() feels more like a function to use when you deal with the manipulation of lists. We keep it simple in this blog, maybe too simple for the power of this function. First we explore how we in a a different column get the adapted version and keep the original untouched. Second we do the replacement in the column that contains the part to be replaced.
Replace something in another column
We reference the column that contains the element we want to replace and apply the replacement function to get the result we look for. The start of our replacement we get via Find() This function gives us the position of the first character of the string we look for. If it does not exist, the function returns minus 1 (-1) as you can see in the above screens. In this case we want nothing to happen, but if the outcome is greater than or equal to one, we start the replacement . Second we need the amount of charcters we want to replace. For this we count the characters in the replacement text using Lenght() Last but not least we provide the replacement text or number. In our case this is ‘.Gent’ but it could also be the outcome of a function.
We start with— as you might have noticed — an If() to check the outcome of the Find() function and if 1 or more, we continue with the replacement, if not we stay with the value as is presented. Below the function we developed:
If(thisRow.domain.Find(".be") >= "1" ,thisRow.domain.ToText().Replace(thisRow.domain.Find(".be"),Length(".be"),".gent"),thisRow.domain).Hyperlink()
The advantage of this approach is two fold. You select the column you want to work at and second this column remais untouched. The downside is that you have an extra column you might not need with a kind of duplicated data. This you can avoid by using a button that modifies the rows that fit criteria.
In this approach you need to check row per row and if needed, execute the replacement. We create — like with Substitute()— per row a button and we ask each button to check her row. These buttons we direct via a RunActions() command in a button outside the table.
The function in the buttons is this:
thisRow.ModifyRows(
thisRow.domain,If(thisRow.domain.Find(".be") >= "1",thisRow.domain.ToText().Replace(thisRow.domain.Find(".be"),Length(".be"),".gent"),thisRow.domain)
)
Conclusion on Substitute, Find & Replace
Find and Replace is easy with Substitute() and I guess more stable, because you can keep the source data. An advantage that cannot easily be overrated. This points to the importance of data integrity. Doesn’t everbody remember you had to look back for the original copy because you made a replacement that turned out to mess up everything? Using Find() & Replace() turns out to be a complicated solution when you only want to replace text. It is a combination of functions that matches more abstract manipulations, not shown here. The other option RegexReplace() I left out as well, it is as Joost writes “notoriously hard to use, but extremely powerful. […] For replacing more than one instance of a character or word you need to use RegexReplace().”
I hope you enjoyed this article. If you do not follow the logic, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done. I live in Ghent and serve the BeNelux market.
