Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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.

Best Answers

  • Community Champion
    Answer ✓

    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

  • Community Champion
    Answer ✓

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

Answers

  • Community Champion

    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

  • Community Champion

    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.

  • Community Champion

    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.

  • Community Champion
    Answer ✓

    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!

  • Community Champion
    Answer ✓

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

  • ✭✭✭✭

    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 + "]"
    
  • Community Champion

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions