Blogs

The sheer power of the SearchAndReplace function

By Brian Cafferelli posted 02-22-2021 15:58

  

The sheer power of the SearchAndReplace function

One of the ways Quickbase empowers non-coders to build their own business solutions is through our Excel-like formula language. The SearchAndReplace function raises the bar by making it easier to manipulate text data. Aside from replacing all appearances of one word with another word, what is it that makes SearchAndReplace special?

  • First, you can use SearchAndReplace to remove all appearances of a word or phrase from a piece of text. You can do this by using a pair of empty quotes. This can be useful for filtering out noise in your data. For example, some companies sync in email messages into their Quickbase apps. You could use SearchAndReplace to remove HTML tags from the body of an email message. This formula would remove all appearances of the term <p> from the Email Body field:

    SearchAndReplace([Email Body],"<p>","")

  • You can also use the SearchAndReplace function several times in a row. This allows you to replace several words or phrases in the same piece of text. For example, this formula would search the Notes field. Then, it would replace those three acronyms with the phrases they represent:

    SearchAndReplace(SearchAndReplace(SearchAndReplace([Notes],
    "GTM","Go-to Market"),
    "KPI","Key Performance Indicator"),
    "YoY Growth","Year-over-Year Growth")

  • Finally, you can use SearchAndReplace with other functions like Left and Right. Let’s say you wanted to exclude all text in a field up to the first appearance of the word “Approved”. This could help you examine an approval log and determine whether multiple approvals have been recorded. Using just the Left/Right/NotLeft/NotRight functions, this would not be possible since they only operate on a single character at a time. But what if you first replaced a word with a single character, and then use a function such as NotLeft? First, select a character which does not appear in your source field. Let’s say that # doesn’t appear anywhere in my source data. Below you’ll find a checkbox formula. This can determine whether the word Approved appears more than once:

    If(Contains(NotLeft(SearchAndReplace([Approval Log],"Approved","#"),"#"),"#") = false,
    true,

    false)

I’ve worked at Quickbase for nine years, and this community has always impressed me with its creativity. Have you tried out SearchAndReplace yet? I’d love to hear what problems you’ve been able to overcome using this function.

Permalink