Skip to main content
Solved

IF, contains formula help!

  • October 13, 2021
  • 2 replies
  • 89 views
T

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.

Best answer by Justin_Barrett

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

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • Answer
  • October 13, 2021

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 & " " & {#})
T
T
  • TiffanyZavala
  • Author
  • New Participant
  • October 21, 2021

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.

Terms & ConditionsAccessibility statement