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.

  • odd LEN($Label.LineBreak)=4 for me. Did you mean LEN(SUBSTITUTE($Label.LineBreak,'-',''))=2 ? – cropredy Jul 1 '16 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 '16 at 14:31
  • 1
    @crmprogdev Good idea, but REGEX(BillingStreet, ".+\n.+\n.+") didn't work in a Validation Rule... – Adrian Larson Jul 1 '16 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 '16 at 20:20
  • 1
    I tried that also. Doesn't change a thing. @crmprogdev – Adrian Larson Jul 1 '16 at 20:52
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

| improve this answer | |
  • Wow. I don't know what else to say. Just wow. – Adrian Larson Jul 11 '16 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.