Results 1 to 4 of 4
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Combining Several Cells into One (2003)

    I have a worksheet that contains names and addresses, with separate fields (columns) for Name, Address, Address2, City, State, and ZIP.

    I need to make a column that has all that information on four lines within one field (the cell will contain a four-line address). I know to use ALT+Enter to make separate lines when keyboarding into a cell, and I know how to make string formulas to do things like combine the existing City, State and ZIP fields into one properly spaced and punctuated line within a single field/cell.

    But I don't know how to make the formula combine the Name and Address fields (for example) into one field so that they appear on two lines within the cell.

    Can anybody help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Several Cells into One (2003)

    You can concatenate with CHAR(10). This is the linefeed character inserted when you type Alt+Enter. For example, to concatenate the contents of A1 and B1 on two lines:

    =A1&CHAR(10)&B1

    Don't forget to turn on Text Wrap in the Alignment tab of Format | Cells...

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining Several Cells into One (2003)

    Assuming the cells below:
    Name(A1), Address(B1), Address2(C1), City(D1), State(E1), and ZIP(F1)

    This will put into 1 cell with wrapping:
    =A1&char(10)&b1&char(10)&c1&char(10)&d1&", "&e1&" "&f1

    The <alt><enter> inserts a linefeed [=char(10)] into the line.

    Steve

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Combining Several Cells into One (2003)

    Thanks, guys, and thank heaven for Woody's Lounge!

    I thought it was probably something easy. Without the lounge, I could have spent half a day looking for it, though.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •