Formatting issue with my rowID field, anyone notice anything like this before?
![Matthew L](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I've used this same formula in a few sheets now. In the past it's looked like the first screenshot below. Using the same formula in any new sheet I create it's now putting the expected values for the second line on a third line below the "-", seen in the second screenshot.
Here's my formula:
=Account@row + "- " + [Story Category]@row + " [Look " + [Look Number]@row + "]"
Does anyone know what could be causing this difference? It's the same formula and I can't tell that there are any formatting differences for the column.
This added line break is just making my rows much longer than they need to be.
Best Answers
-
Hi @Matthew L
If the Story Category is a multi-select drop down, each value will have a line break associated with it. This is how I believe you are achieving the line break before the hyphen and is what I think is causing the line break after the hyphen. Your other sheets might not be multi-select drop downs.
If this is the case, you can remove the line break using a combination of LEFTH and LEN to return only the left hand content of the cell. Like this:
=Account@row + "- " + LEFT([Story Category]@row, LEN([Story Category]@row)) + " [Look " + [Look Number]@row + "]"
Hope that helps
-
That is excellent. If you want to have all the options from the multiselect on one row you could replace the line break with a space (or something else) using the SUBSTITUTE function:
=Account@row + "- " + SUBSTITUTE([Story Category]@row, CHAR(10), " ") + " [Look " + [Look Number]@row + "]"
This would use a forward slash instead of the space - you can use anything you like.
=Account@row + "- " + SUBSTITUTE([Story Category]@row, CHAR(10), "/") + " [Look " + [Look Number]@row + "]"
Answers
-
Is the column slightly narrower than usual?
-
@KPH, Column size is the same. But even if I extend it and make it as wide as possible it's still adding that extra line break. See screenshot below
-
Do you have something in
Account@row
that is creating the first (intentional) line break?
-
@KPH eComm is the value in Account@row. There's no formula in that row, that row consists of drop down values.
-
OK, so that must be a multi-select drop down. The multi-select aspect will be adding a line break at the end of eComm which is how you get the new line before the hyphen.
So, I am wondering if Story Category is also a multi-select drop down.
-
Hi @Matthew L
If the Story Category is a multi-select drop down, each value will have a line break associated with it. This is how I believe you are achieving the line break before the hyphen and is what I think is causing the line break after the hyphen. Your other sheets might not be multi-select drop downs.
If this is the case, you can remove the line break using a combination of LEFTH and LEN to return only the left hand content of the cell. Like this:
=Account@row + "- " + LEFT([Story Category]@row, LEN([Story Category]@row)) + " [Look " + [Look Number]@row + "]"
Hope that helps
-
@KPH , Thanks! That worked for the most part! It's still adding the line break when there's multiple selections in the Story Category but this helped solve the issue for all the rows with only one entry for that field so this is huge!
-
That is excellent. If you want to have all the options from the multiselect on one row you could replace the line break with a space (or something else) using the SUBSTITUTE function:
=Account@row + "- " + SUBSTITUTE([Story Category]@row, CHAR(10), " ") + " [Look " + [Look Number]@row + "]"
This would use a forward slash instead of the space - you can use anything you like.
=Account@row + "- " + SUBSTITUTE([Story Category]@row, CHAR(10), "/") + " [Look " + [Look Number]@row + "]"
-
Ok, I like it. Picasso!
Thanks so much @KPH!!! I used the forward slash formula that you shared and this looks infinitely better than what I had before for these ROWIDs!!!
=Account@row + "- " + SUBSTITUTE([Story Category]@row, CHAR(10), "/") + " [Look " + [Look Number]@row + "]"
-
That's great news. I'm pleased I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!