Document Builder - Mailing Label
Hi!
I'm trying to build a mailing label from multiple columns and need a carriage return after each line. I have a formula I've written that works except that it doesn't suppress a null column and move the next line up.
Here’s the formula for Column 11:
=[Address 1]@row + CHAR(10) + [Address 2]@row + CHAR(10) + City@row + ", " + State@row + " " + Zip@row + CHAR(10) + [Address 2]@row + CHAR(10) + [Address 4]@row + CHAR(10) + [Address 5]@row + CHAR(10) + [Address 6]@row + CHAR(10)+ [Address 7]@row
In this example, if [Address 2] is blank, it would return this result in Document Builder:
Address 1
City, State zip
Address 4
Address 5
Address 6
Address 7
Where Address 2 is a blank field and an empty space in the mailing label. I want to suppress Address 2 and everything else moves up.
Answers
-
Another Community member may have a cleaner approach, but the way I would work with this is to embed your output formula into either one or multiple IF statements.
For example:
I would say, IF Address 2 is blank, return your formula without Address 2, otherwise, return your formula as-is, including Address 2.
Like so:
=IF([Address 2]@row = "", (formula without address 2), (formula with address 2))
Full formula:
=IF([Address 2]@row = "", ([Address 1]@row + CHAR(10) + City@row + ", " + State@row + " " + Zip@row + CHAR(10) + [Address 4]@row + CHAR(10) + [Address 5]@row + CHAR(10) + [Address 6]@row + CHAR(10)+ [Address 7]@row), ([Address 1]@row + CHAR(10) + [Address 2]@row + CHAR(10) + City@row + ", " + State@row + " " + Zip@row + CHAR(10) + [Address 2]@row + CHAR(10) + [Address 4]@row + CHAR(10) + [Address 5]@row + CHAR(10) + [Address 6]@row + CHAR(10)+ [Address 7]@row))
However, this is only looking to see if specifically Address 2 is blank. Would you need to check all the other address columns/cells as well? We could build a nested IF statement in this case, but I would need to know all of the potential blank cells in order to help you do so.
Let me know if this makes sense or if I've misunderstood what's needed!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!