Formatting issue with my rowID field, anyone notice anything like this before?

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.

Find more posts tagged with

Sheets

Formulas

formatting

Accepted answers

KPH

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

@Matthew L

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 + "]"

All comments

KPH

Is the column slightly narrower than usual?

Matthew L

@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

KPH

Do you have something in

Account@row

that is creating the first (intentional) line break?

Matthew L

@KPH eComm is the value in Account@row. There's no formula in that row, that row consists of drop down values.

KPH

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.

KPH

Hi @Matthew L

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

Matthew L

@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!

KPH

@Matthew L

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 + "]"

Matthew L

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 + "]"

KPH

That's great news. I'm pleased I could help!

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.