Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Does anybody know if this post from 3 years ago has been addressed?
https://community.smartsheet.com/discussion/new-line-alt-enter-formulas
I'm looking to concatenate cells with line breaks. Thanks!
Hi Tyler,
Use the instructions below to add a line break in a string created by formula (using JOIN or "+" symbols)
1. Create a column called "Helper" and put two dashes separated by a blank line in row 1: -
-
2. To use with JOIN, set this function as the delimiter: "SUBSTITUTE(Helper$1, "-", "")3. To use in a manual string, enter this to add a line break: "+ SUBSTITUTE(Helper$1, "-", "") +"
Enable word-wrap on any cell where you use this technique. The helper cell can't be deleted. Word wrap must be enabled on cells that use this technique.
Thanks, Kara. Could I get some screenshots showing the instructions above? Also, I'm not sure which of the two options would be best for my scenario (either the + symbol or JOIN.
Thank you!
Here you go!
Brilliant!
Paul,
What will be brilliant is just doing a UNICHAR(13)
I had no idea what that was. I did a little research, and yes. That will be brilliant.
Great! But does this work when wanting to "join" different cells within the same row? I see your example joins Ziggy, Zoey, and Dobby which are rows 1, 2, 3. I'm looking to create a joined cell with these line breaks you're showing, but for each row with selected data points in separate columns....if that makes sense...see my screenshot showing that my concatenated formula doesn't line break.
Try something like this.
The Double Carriage cell/column would look like below:
=JOIN([Host Agency Name]@row:[AgencyCountry]@row; SUBSTITUTE([Double Carriage]@row; "-"; ""))
The same version but with the below changes for your and others convenience.
=JOIN([Host Agency Name]@row:[AgencyCountry]@row, SUBSTITUTE([Double Carriage]@row, "-", ""))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
This should be the result (without the space between the rows):
Willacy County EMS
Phone
693 South 7th
Raymondville
TX
78550
USA
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
Thanks, Andree, but could you take a look at my screenshot and tell me what I'm missing?
Happy to help!
Just change the @1 to @row instead. I added @row because then you don't need to think about row numbers.
Andrée
Here's another screenshot...still to no avail...
Hey Tyler,
This works:
=SUBSTITUTE(Helper$1, "-", "") + [Host Agency Name]@row + SUBSTITUTE(Helper$1, "-", "") + [Host Agency State]@row
When you add in a state, it will do a line break
SUCCESS!! Thank you so much!
It should work!
Are all the columns placed beside each other? Are the column names correct?
Can you maybe share the sheet(s)? I'd be happy to take a look and see what the problem is. (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I think I know why it didn't work.
I didn't see that In your formula you used semi-colons but I think that where you are located it should be as Karas and commas instead.
Try this one.
=JOIN([Host Agency Name]@row:[Host Agency State]@row, SUBSTITUTE([Double Carriage]@row, "-", ""))
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
