Excel - Formatting Concatenation of Cells

Posted on 2011-10-07
Last Modified: 2012-05-12

Is there a way to combine cell contents across a row to be merged into one? For example:

A1: 2800
B1: 2345

Usually, I would create a formula:

"A1" & " " & "B1" & " " & "C1" to be merged into one cell that contains the formula:

D1: 2800 2345 78598

Please note the space between each cell content. The nuance is that the number of cells to be combined is variable.

Thank you
Question by:tahirih
    LVL 50

    Accepted Solution

    There's no simple way to concatenate a range (without VBA) without adding each cell separately, assuming a maximum of 5 values with no spaces in the cells you could do this

    =TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)

    TRIM removes any excess spaces....

    regards, barry

    Author Comment

    Can you please offer a VBA solution?

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now