14

I noticed that the Contact.MailingStreet field can be accessed line-by-line in reports with the Mailing Address Line 1, Mailing Address Line 2, and Mailing Address Line 3 fields.

I want to create formula fields that are the equivalent of these fields, using the FIND, LEFT, MID, and RIGHT functions to break out the street address line by line. I have tried various new line variations (\n, \r\n, <br />) but the FIND function doesnt seem to pick up on new lines.

Any thoughts?

Improve this question
6
  • Why not just access the individual fields?
    – Mike Chale
    Oct 28, 2013 at 17:48
  • 1
    Mailing Address Line 1, 2, 3 only exist in reporting and are not actual fields.
    – Phil Rymek
    Oct 28, 2013 at 17:51
  • 1
    Geez, i remember having this problem and I see I solved it with three custom fields: street_1__c, street_2__c, street_3__c and using a before insert/update trigger that transformed mailingStreet using a split('\n') into an array and then copying array[0] to street_1__c, array[1] to street_2__c and all remaining array elms to street_3__c
    – cropredy
    Oct 28, 2013 at 18:04
  • 3
    The trigger method is correct. Normal formula fields cannot see newlines (but validation rules can detect newlines via REGEX, which normal fields do not get access to).
    – sfdcfox
    Oct 28, 2013 at 18:30
  • Darn, not even FIND(BR(), MailingStreet) works, had a sneaky hope it might.
    – Christopher Alun Lewis
    Jan 6, 2014 at 21:58

3 Answers 3

Reset to default
7

Make a Custom Setting, type Hierarchy. This setting needs to have a field in it that has the value:

a
b

In this answer, my custom setting is Global__c and my field is CRLF__c.

What characters you use doesn't matter, but the important thing is one character on the first line, and one character on the second line. The Length of this custom setting is actually 4 characters. Two printable, a and b, and two that don't have a glyph, Carriage Return aka CR aka \r and Line Feed aka LF aka \n.

MID($Setup.Global__c.CRLF__c, 3, 1) is now equivalent to a Line Feed character in the context of a formula. We can abuse this to do things like get the first line of an address field.

IF(
  CONTAINS(MailingStreet, MID($Setup.Global__c.CRLF__c,3,1)), 
  LEFT(
    MailingStreet, 
    FIND(MID($Setup.Global__c.CRLF__c,3,1), MailingStreet)
  ),
  MailingStreet
)
Improve this answer
2
  • 2
    Wow, thank you, thank you, thank you. What a cool solution. Here is my write up as to how I used your solution tdd.instawiki.com/display/SF/aiEasyLabel
    – JodieM
    Jun 10, 2016 at 10:38
  • 2
    This is a really clever solution. Just implemented it, with the slight variation of using a custom Label instead of a Custom Setting. That way, I can deploy the whole lot together.
    – Aidan
    Aug 10, 2017 at 11:48
3

You could try something to the effect of:

String Line1, Line2, Line3;
String[] lines = addressFormula.split('\r\n');
Line1 = lines[0];
if (lines.size() > 1){
    Line2 = lines[1];
    if (lines.size() > 2){
        Line3 = lines[2];
    }
}
Improve this answer
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
   )
)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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