Combining cells to include a return/enter

Tina Chamblee · 10/14/21

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.

Genevieve P. · 10/15/21

Hi @Tina Chamblee

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)

Screen Shot 2021-10-15 at 6.18.17 PM.png

Let me know if this makes sense and if it returns the desired output!

Cheers,

Genevieve

Genevieve P. · 10/15/21