Skip to main content

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.



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 & " " & {#})


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.

Reply


Terms & Conditions