[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Access - Combine Cells Across A Row

Posted on 2011-10-20
Medium Priority
266 Views
Hi

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

Example:

A1: The
B1: Dog
C1: Is
D1:Brown

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.

Thank you.
0
Question by:tahirih
• 3
• 2

LVL 49

Expert Comment

ID: 37002928
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?
0

LVL 74

Expert Comment

ID: 37003020
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.

JeffCoachman
0

LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 37003098
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&" ")&".")

...

Jeff
0

Author Comment

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

Author Closing Comment

ID: 37008657
Thank you.
0

LVL 74

Expert Comment

ID: 37008921
;-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
###### Suggested Courses
Course of the Month18 days, 14 hours left to enroll