Document Builder - Mailing Label

Jeff Fielder

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.

Genevieve P.

Hi @Jeff Fielder

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