Help

IF, contains formula help!

Topic Labels: Formulas
Solved
Jump to Solution
1547 2
cancel
Showing results for 
Search instead for 
Did you mean: 
TiffanyZavala
5 - Automation Enthusiast
5 - Automation Enthusiast

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
Justin_Barrett
18 - Pluto
18 - Pluto

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
Justin_Barrett
18 - Pluto
18 - Pluto

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.