13

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?

  • Why not just access the individual fields? – Mike Chale Oct 28 '13 at 17:48
  • 1
    Mailing Address Line 1, 2, 3 only exist in reporting and are not actual fields. – Phil Rymek Oct 28 '13 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 '13 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 '13 at 18:30
  • Darn, not even FIND(BR(), MailingStreet) works, had a sneaky hope it might. – Christopher Alun Lewis Jan 6 '14 at 21:58
4

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
    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 '16 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 '17 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 | |

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.