Combining cells to include a return/enter
I am trying to put our mailing addresses in one cell to join from other cells we have in the sheet and need to figure out how to put a return after each. Below is the cells I am bringing final information from:
I need it to appear as follows in a separate column so we can have this put into a report for others to use for labels/letters (of course without the blank line after:
Jon Mikesell
101 W. 1st Street
Clearfield, PA 16803
Thanks for your help.
Best Answer
-
CHAR(10) creates a line break, so you can add together your cells with the + symbol and add lines/spaces/commas etc, using quotes, as needed.
Try something like this:
=[LO Mailing Name]@row + CHAR(10) + [LO Mailing Address 1]@row + CHAR(10) + [LO Mailing Address 2]@row + CHAR(10) + [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row
Then you can make this a Column Formula.
Now, this will return an extra space/line for the Address 2 column. If we want to get rid of that, we can add in an IF statement in the middle, that says if it's blank, continue on to the rest of the formula. But if it's not blank, add in another space and details.
Full Formula:
=[LO Mailing Name]@row + CHAR(10) + [LO Mailing Address 1]@row + CHAR(10) + IF([LO Mailing Address 2]@row = "", [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row, [LO Mailing Address 2]@row + CHAR(10) + [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row)
Let me know if this makes sense and if it returns the desired output!
Cheers,
Genevieve
Answers
-
CHAR(10) creates a line break, so you can add together your cells with the + symbol and add lines/spaces/commas etc, using quotes, as needed.
Try something like this:
=[LO Mailing Name]@row + CHAR(10) + [LO Mailing Address 1]@row + CHAR(10) + [LO Mailing Address 2]@row + CHAR(10) + [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row
Then you can make this a Column Formula.
Now, this will return an extra space/line for the Address 2 column. If we want to get rid of that, we can add in an IF statement in the middle, that says if it's blank, continue on to the rest of the formula. But if it's not blank, add in another space and details.
Full Formula:
=[LO Mailing Name]@row + CHAR(10) + [LO Mailing Address 1]@row + CHAR(10) + IF([LO Mailing Address 2]@row = "", [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row, [LO Mailing Address 2]@row + CHAR(10) + [LO Mailing City]@row +", " +[LO Mailing State]@row + " " + [LO Mailing Zip]@row)
Let me know if this makes sense and if it returns the desired output!
Cheers,
Genevieve
-
That worked, thanks so much. Couldn't wrap my head around the formula.
-
No problem! I'm glad I could help. 🙂
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives