[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
6
Medium Priority
?
266 Views
Last Modified: 2012-05-12
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.

Hope this made sense.

Thank you.
0
Comment
Question by:tahirih
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Dale Fye
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

by:Jeffrey Coachman
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

by:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

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

Author Closing Comment

by:tahirih
ID: 37008657
Thank you.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37008921
;-)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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

834 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