Access - Combine Cells Across A Row

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

Please offer the most efficient manner to add text entries across a row -(e.g 2nd row) m-that are filled with text values.


A1: The
B1: Dog
C1: Is

I then have a formula that combines all to say "The Dog Is Brown."

The nuance is that there may be 4 cells across, or 20 - so the combined values may be in Cell Z1.

Hope this made sense.

Thank you.
Question by:tahirih
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    You indicate an "MS Access Database" zone, but it sounds like you are talking about Excel.

    Do you want an excel macro for this, or are you doing something in Access to a linked Excel worksheet?
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    You could try something like this in the cell where their final text will appear:

    =TRIM(A1&" "&B1& " " &C1&" "&D1&" "&E1&" "&F1&" "&G1&" ") & "."

    This would be in Cell H1 and could be filled down for the other rows.

    So for the final text to be in Z1 you would have to load the formula all the way to Y1.
    The trim function will trim off the extra spaces, then the Period (".") will be added to the end.

    LVL 74

    Accepted Solution

    This will allow for blank rows:

    =IF(LEN(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "))=0,"",TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" ")&".")



    Author Comment

    Thank you everyone - please give me a bit to try everything - and I will finsih this question.

    Author Closing Comment

    Thank you.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now