[SalesForce] Count Line Breaks

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.

Best Answer

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

Related Topic