Ask Search:
App SharkApp Shark 

How to remove line breaks from billing street formula field

I have a requirement to show billing Street in a single line.
For this i have created a formula field (TEXT) and i copied billing street  but it shows the same in 2 lines. how can i achieve this to show in single line.
Best Answer chosen by Miglena (Salesforce.com) 
edgar Zamoraedgar Zamora
Actually Vinay came close. Here is the solution to remove line break in the billingstreet address.

Step 1. Create a custom label and name it linebreak ith a value of " 
-
-" do not include the ".

Step 2. use this formula on your formula field#

SUBSTITUTE( BillingStreet ,SUBSTITUTE($Label.linebreak, "-", "")," ")


 

All Answers

Pritam ShekhawatPritam Shekhawat
Hello App,
               Unfortunely you can't remove line break from formula. You can upvote and promote this idea here Ability to remove line breaks in formula fields (https://success.salesforce.com/ideaview?id=08730000000BqBQAA0)  

Some workaround : https://developer.salesforce.com/forums/?id=906F00000008w7kIAA

Thanks,
Pritam Shekhawat
Vinay ChaturvediVinay Chaturvedi
Hello App Shark,
Where do you want to display this field?
On a VF Page or a record detail page?
Vinay ChaturvediVinay Chaturvedi
++.
I believe you can match on Line Breaks using SFDC's REGEX() function. You just have to do the following:

1. Escape the new-line character with an additional backslash (because it's being used in a string which gets compiled to a Regular Expression)

2. Surround your search text with .* (so that you get partial string matches)

So, for example, to test that Shipping Street is only one line, use this:

REGEX(ShippingStreet,'.*\\n.*')
 
App SharkApp Shark
Hi Vinay Chaturvedi,
 Thanks for reply. I want to show it on detiled page.
App SharkApp Shark
Hi Vinay,
I got an error when using REGEX. (REGEX( BillingStreet ,'.*\\n.*'))
Error: Function REGEX may not be used in this type of formula
Vinay ChaturvediVinay Chaturvedi
Oops,
Was just searching for a better solution .
This is I got from one of the commenst somewhere:

" needed to add a line break to a formula field.

The BR() function inserts an HTML line break, so it does not work in places where you need a plain line break.

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, "-", ""),"")
"

Can you try this once?
App SharkApp Shark
Hi Vinay,
I tried but it wont works.
慶太 米澤慶太 米澤
check this link. it's easy and awesome. 
https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001rzcCAA&fId=0D530000029T7FqCAK
edgar Zamoraedgar Zamora
Actually Vinay came close. Here is the solution to remove line break in the billingstreet address.

Step 1. Create a custom label and name it linebreak ith a value of " 
-
-" do not include the ".

Step 2. use this formula on your formula field#

SUBSTITUTE( BillingStreet ,SUBSTITUTE($Label.linebreak, "-", "")," ")


 
This was selected as the best answer
Peter RozekPeter Rozek
Edgar's answer works like a charm.  I would point out that the custom label is a dash, followd by a carriage return, and then a dash on the second line.  The formatting above made this a bit hard to recognize at first. 
Nicola MitchamNicola Mitcham

Thanks Edgar it worked great for me too (once I'd taken on board Peter's comment about the Custom Label needed - carriage return - (was just getting #error before that), it's now working perfectly!
Cheers
Nic

Chas BurnerChas Burner
This is the sexiest SF workaround I've ever seen.  Edgar... you're number one.
Matt ShonkwilerMatt Shonkwiler
@edgar Zamora and @慶太 米澤 can this workaround be used as the search-text within the FIND() formula?

I'm asking because I'm troubleshooting a Process Builder formula that uses...
FIND(SUBSTITUTE($Label.linebreak, "-", ""), [Task].Description)
...but it keeps returning blank.  So I'm wondering if there's a problem with my formula, or if FIND simply can't search for a return carriage.
Issac ChuaIssac Chua
Edgar's solution works like a charm! Thumbs up!!
Volney DouglasVolney Douglas
The SUBSTITUTE( BillingStreet ,SUBSTITUTE($Label.linebreak, "-", "")," ") suddenly stopped working for me when trying to remove Linebreaks. I think it might be related to the BR update. Does anyone else have this problem?