7

I am trying to count the number of lines in the BillingAddress field. I am familiar with the following methodology to count the number of times a substring occurs via formula:

LEN(TextField__c) - LEN(SUBSTITUTE(TextField__c, "substring", "")

Cool, should be pretty straightforward to use that, right? Just replace "substring" with a newline character, add 1 to the result, and you're done. Wrong.

Here is a list of values I tried to use in place of "substring":

  • "\n"
  • "\\n"
  • "\r\n"
  • BR()
  • $Label.LineBreak
    • \n
    • \\n
    • \r\n
  • SUBSTITUTE($Label.LineBreak, "-", "")
    • -\n-

None of the above has worked. Some explanation on the $Label options is in order here. In searching for a solution, I found this Idea which contains a particular suggestion by Jesper Kristensen people found helpful.

Instead I ended up making this formula code, which gives me a plain line break.

SUBSTITUTE($Label.LineBreak, "-", "")

You also need to create a Custom Label named LineBreak, which contains two dashes separated by a line break.

This code can also be used to remove line breaks:

SUBSTITUTE(Street,SUBSTITUTE($Label.LineBreak, "-", ""),"")

Additional Testing

I tried changing the formula to LEN($Label.LineBreak), and it outputs 2 with \n, or 4 with -\n-. So the issue does not seem to be caused by the length of newline characters calculating as 0. I also tried setting $Label.LineBreak to \n and using IF(CONTAINS(BillingStreet, $Label.LineBreak), 1, 0), and got back 0. Using the -\n- approach yielded the same result.

It is pretty obvious how to achieve this functionality using Apex, but I want a config only solution. It seems like it should be achievable.

5
  • odd LEN($Label.LineBreak)=4 for me. Did you mean LEN(SUBSTITUTE($Label.LineBreak,'-',''))=2 ?
    – cropredy
    Jul 1, 2016 at 2:36
  • 3
    It would seem that RegEx would be another option to utilize for this as well since you could find the \n and even replace it using a RegEx function or simply retrieve the number of instances returned (not certain on the latter in a formula though, would need to go look at the docs). Not that I think it would be "better", faster or preferable to any other method.
    – crmprogdev
    Jul 1, 2016 at 14:31
  • 1
    @crmprogdev Good idea, but REGEX(BillingStreet, ".+\n.+\n.+") didn't work in a Validation Rule...
    – Adrian Larson
    Jul 1, 2016 at 18:51
  • @AdrianLarson In Apex you would need to escape the backslash. Don't know if that would apply to a validation rule or not.
    – crmprogdev
    Jul 1, 2016 at 20:20
  • 1
    I tried that also. Doesn't change a thing. @crmprogdev
    – Adrian Larson
    Jul 1, 2016 at 20:52

2 Answers 2

3

Make sure you have an actual new line in the Custom Label. It will not work if you enter \n

This will work:

enter image description here

This will NOT work:

enter image description here

1
  • Wow. I don't know what else to say. Just wow.
    – Adrian Larson
    Jul 11, 2016 at 17:50
0

You can also do this directly in a Salesforce Flow. Create a text template variable of type plain text. In the example it is called "text_T_baseForCRLF" enter image description here In the text template write the following:

-
-

Then create a formula with name e.g. name "form_T_CRLF" and with content: enter image description here

SUBSTITUTE({!text_T_baseForCRLF}, "-", "")

Then you can use it in the flow.

In my case i needed to split a question and answer string inside a loop. So i came opp with this formula to get the question part. Then there is another formula to get the answer after the line shift.

TRIM(
   MID({!LOOP_overAnswerGroups}, 
   FIND("Q.", {!LOOP_overAnswerGroups})+2, 
   FIND(form_T_CRLF, {!LOOP_overAnswerGroups})-4
   )
)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .