Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

IF, contains formula help!

Topic Labels: Formulas
Solved
Jump to Solution
789 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello!

I am trying to troubleshoot a formula field.

Currently I have this in the formula field: CONCATENATE({Part}," | “,{Objective},” ",{#})
to create the following:
Screen Shot 2021-10-12 at 6.01.26 PM

Looking at the first record, the result reads: Part 1: Competent Leader | Heart 1

However, I would like create a search that allows the final formula to read as such:

Part 1 | Heart 1

I want it to be able to search the {Part} field, but not list the entire title - if that makes sense. Seems simple enough, but I had problems using the Find or Search field.

1 Solution

Accepted Solutions

This can be done using either the FIND() or SEARCH() function (not field). I’ll use FIND() in this example.

First to extract the beginning of the {Part} field. I’ll start with an IF() function just to make sure that something is selected there, and then remove the characters on the left that fall before the colon:

IF(Part, LEFT(Part, FIND(":", Part) - 1))

The FIND() function is going to return the position of the colon character. We subtract 1 from that, and that is how many characters the LEFT() function returns from the start of the string.

With that done, the rest can be borrowed from your original formula, though I’m going to use the concatenation operator—the & character—instead of the CONCATENATE() function.

IF(Part, LEFT(Part, FIND(":", Part) - 1) & " | " & Objective & " " & {#})

See Solution in Thread

2 Replies 2

This can be done using either the FIND() or SEARCH() function (not field). I’ll use FIND() in this example.

First to extract the beginning of the {Part} field. I’ll start with an IF() function just to make sure that something is selected there, and then remove the characters on the left that fall before the colon:

IF(Part, LEFT(Part, FIND(":", Part) - 1))

The FIND() function is going to return the position of the colon character. We subtract 1 from that, and that is how many characters the LEFT() function returns from the start of the string.

With that done, the rest can be borrowed from your original formula, though I’m going to use the concatenation operator—the & character—instead of the CONCATENATE() function.

IF(Part, LEFT(Part, FIND(":", Part) - 1) & " | " & Objective & " " & {#})

Thank you so much! This worked perfectly.