Formula is resulting in carriage return and quotes around text

Here is my formula:

=[Client Name (Avi)]@row + "-" + LEFT([Request Category]@row, 1) + "-" + MID(Created@row, 7, 2) + "-" + [Request ID]@row + "- " + JOIN([Services Requested - Utility]@row + [Services Requested - Waste]@row + JOIN(" -P"))

When I copy/paste the cell (or export to Excel), the text looks like this (two rows, with quotes):

"Client Name-O-22-01986- Electric

-P"

I also tried copy/paste into a different sheet, here's the result:

Find more posts tagged with

Accepted answers

Jeff Reisman

https://community.smartsheet.com/discussion/comment/344156#Comment_344156

Yes, that's where your carriage return is coming from. The behind the scenes separator of those multi select values is a carriage return, known in ASCI as CHAR(10). So try using the SUBSTITUTE function to replace carriage returns with blank spaces:

=SUBSTITUTE(([Client Name (Avi)]@row + "-" + LEFT([Request Category]@row, 1) + "-" + MID(Created@row, 7, 2) + "-" + [Request ID]@row + "- " + JOIN([Services Requested - Utility]@row + [Services Requested - Waste]@row + JOIN(" -P"))), CHAR(10), " ")

All comments

Jeff Reisman

@Ami Veltrie This has to be an issue with the underlying data. I created a formula with the same syntax you're using and had no issues. I suspect it's something with the data in the columns referenced in this part:

JOIN([Services Requested - Utility]@row + [Services Requested - Waste]@row + JOIN(" -P"))

Plus, I'm not sure why you're even using JOIN here, since you're not specifying a range or delimiters. Taking out the JOINs results in the same text format:

Ami Veltrie

@Jeff Reisman Hello. Thank you for your response.

Using JOIN in another instance where I was getting the 'wrapped' text fixed the problem so I was trying to replicate the fix.

Could the issue be caused by 'multi-select' column? "Services Request - Utility" and "Services Requested - Waste" are multi-select columns.

If so, how do I fix it?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/344156#Comment_344156

Ami Veltrie

@Jeff Reisman You are a lifesaver! Thank you!!

Ami Veltrie

I removed the JOINs to clean it up. As you said, they aren't needed:

=SUBSTITUTE(([Client Name (Avi)]@row + "-" + LEFT([Request Category]@row, 1) + "-" + MID(Created@row, 7, 2) + "-" + [Request ID]@row + " - " + ([Services Requested - Utility]@row + [Services Requested - Waste]@row)) + "-P", CHAR(10), " ")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.