# IF, contains formula help!

Topic Labels: Formulas
Solved
1378 2
cancel
Showing results for
Did you mean:  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: 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  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 & " " & {#})
``````
2 Replies 2  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 & " " & {#})
``````  5 - Automation Enthusiast

Thank you so much! This worked perfectly. 