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:
Best Answer
-
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), " ")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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?
-
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), " ")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman You are a lifesaver! Thank you!!
-
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), " ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
