+ Start a Discussion
Paul.FoxPaul.Fox 

How to remove a line break in a formula field

Does anyone know how to remove a line break from a field?
I am trying to use MailingAddress in a hyperlink formula but it breaks every time there is a contact with a two line mailing address.

I have tried the following and they did not work:
substitute(MailingAddress,BR(),"")
substitute(MailingAddress,"\n","")
substitute(MailingAddress,\n,"")
trim(MailingAddress)

For reference my formula looks something like this:
hyperlink("http://linklocation.com?address="&MailingAddress,"Link Text", "_blank")
Mr SaaSMr SaaS
Paul did you ever manage to resolve this please?
 
I am facing the same problem.
 
Many thanks
Paul.FoxPaul.Fox
I never found a solution to do it with just a formula field, but I am using a workaround currently.
Since custom links automatically remove the line break I created a custom link that I wanted. Then, I used the custom link in my hyperlink formula field.

The end result looked like this:

Custom Link:
http://www.jigsaw.com/SFL.xhtml?opCode=addsingle&sfldid={!Lead.Id}&cnEmail={!Lead.Email}&cnTitle={!Lead.Title}&cnFirstname={!Lead.FirstName}&cnLastname={!Lead.LastName}&cnPhone={!Lead.Phone}&cnAddress={!Lead.Street}&cnCity={!Lead.City}&cnState={!Lead.State}&cnZip={!Lead.PostalCode}&cnCountry={!Lead.Country}&sfsession={!API.Session_ID}&sfserver={!API.Partner_Server_URL_60}&sfuserid={!User.Id}&sfusername={!User.Username}

Formula Field:
HYPERLINK(
"/servlet/servlet.Integration?lid=00b30000000sOKk&eid="& Id &"&ic=1",
"Update",
"_blank")

This works fine, but it requires that you create a custom link that you're not going to use just so you can make a working hyperlink function.
Paul.FoxPaul.Fox
For anyone looking at this, I finally got a response from salesforce support:

At this time, this is expected behavior from the salesforce.com application.  I do apologize for any inconvenience that this may cause.

If this is something that you would like to see in salesforce, vote for my ideaexchange post.
MKIMKI

Any news/solution on this issue?

cgardiner70cgardiner70

I don't know if this is a new solution - more of a new hack. It only works on Rich Text fields.

 

I created a hidden rich text field (TEMP1) and, using a trigger, pre-populated it with the value "a<br><br><br>a".

 

Then, I created a second rich text field (ORIGINAL1) and created a workflow rule that updated ORIGINAL1 whenever the record was created or updated. The field update used the SUBSTITUTE formula combined with the MID formula to replace the line break from the middle of the TEMP1 field with any line breaks it found in the ORGINAL1 field.

 

SUBSTITUTE( ORIGINAL1 , MID( TEMP1 , 1,1) , "; ")

 

Like I said, it works, but it's a total hack.

aleahaleah

This worked for me:

 

var test = "{!JSINHTMLENCODE(ACA_Production_Process_Request__c.Return_Email_Recipients_Other__c) }".replace(/\n/g,"");

 

ACA_Production_Process_Request__c.Return_Email_Recipients_Other__c field is a Text Area with line breaks

tes2tes2
Was able to workout a formula hack for finding a crlf,  it aint pretty but was able to get it to work.  It uses a custom setting field with a crlf and parse it out.

Ex: 
FIND(MID($Setup.Global__c.CRLF__c,2,1),BillingAddress)
The Org wide value for Global__c.CRLF__c (text area) is Char+CRLF+Char

Ex:
A
B


Christina Moore 13Christina Moore 13
@tes2 your hack above worked perfectly for me.  Thanks!
PJR-03820PJR-03820
https://success.salesforce.com/answers?id=90630000000DNcNAAW Also works well.