Genius Simple Trick to Eliminate Repeated Spaces in Salesforce Formulas

Neat trick that can be used in Salesforce formulas.

It converts multiple spaces between words into a single space. This kind of cleaning would usually be done in Apex.

The ~ and ^ could be any character but these were preferred because they are the least used in normal text and can still be typed.

Charles Naccio (Salesforce Architect) used the following alternative to remove consecutive line breaks from an address field:

TRIM(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(SUBSTITUTE(Shipping_Street__c, SUBSTITUTE($Label.NewLine, “-“, “”), ” “), ” “, “~^”),
        ‘^~’,
        ”
      ),
      ‘~^’,
      ‘ ‘
    )
  )

The NewLine custom label is a workaround to detecting line breaks; the labels value is:
–
–