function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
bob17bob17 

BR() in Formula results in _BR_ENCODED_ in Excel

When I use BR() in a Formula field to create a line break the value looks fine ion screen but when used in a report that is exported to excel the line break gets translated to "_BR_ENCODED_". 

 

The same thing does not happen when the report goes to Excel via the Printable View button but that can't substitute for the Export option.

 

One other post seemed to indicate this this was addressed way back in Winter 07' but that does not seem true now. 

 

The Export to CSV via reporting also replaces the line break with the "_BR_ENCODED_" test string.

 

Anyone know if this is a bug or SF really intended for it to work this.  Also, anyone have a work around to this problem?  Would have thought it would be more common given there is no native Address data type and lots of people would be creating formula based formatted address fields.

 

Thanks

AK1AK1

Did you ever resolve this. Still seems to happen.

bob17bob17

AK1,

 

No.  Tech support said this was how SF was designed and they don't consider it a bug.  Client ended up exporting each of the individual address fields and then putting them back together again into a single in Excel using an Excel formula.  Could probably have done a global search and replace.  Both ways still require manual intervention but client was doing lots of post export formatting because the SF report writter is so weak so one more step didn't seem like such a big deal.

akak

This came up again in another implementation.

 

Found another workaround. After export to excel you can use the following formula to get the field formatted with line feeds recognised by excel.

 

=SUBSTITUTE(A1,"_BR_ENCODED_",CHAR(10))

nb A1 is the cell you are frmatting

 

Still another step, so not ideal, but this is a relatively quick way to get the export field correctly formatted.

Matt UrbanowskiMatt Urbanowski

I've come up with a workaround.
Create a custom label called BR. Set the value of it to:
a
b

Now in the formula field, instead of using BR(), use:
SUBSTITUTE(SUBSTITUTE($Label.BR,'a',''),'b','')
This only works when exporting as CSV though, not Excel.