Combining cells to include a return/enter

Options

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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)



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

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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)



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

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Tina Chamblee
    Options

    That worked, thanks so much. Couldn't wrap my head around the formula.

  • Genevieve P.
    Options

    No problem! I'm glad I could help. 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now