Solved

Concatinate and Trim Column A and B

Posted on 2011-09-13
6
244 Views
Last Modified: 2012-05-12
I am looking for a macro that will work with column A and B.  First I would like to concatenate column A with column B, however column B has unwanted spaces in it, so I need to TRIM out or CLEAN column B either before or after.  Is there an easier way to do this by the column instead of having to copy the formula down and copy/paste specail values etc.  There is no set number of rows, which can change from day-to-day.

Would like to automate this.  Please advise and thanks. -R-
0
Comment
Question by:RWayneH
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:nike_golf
ID: 36530918
This is quick and dirty but you get the idea. The concatenated item is placed in column "C", this also assumes there are no blank rows in colum "A".

Public Sub concat()
Dim rng, x As Range

Set rng = ActiveSheet.Range("a1", Range("A1").End(xlDown))

For Each x In rng
    x.Offset(0, 3) = x & Trim(x.Offset(0, 1))
Next x

End Sub

NG,


0
 
LVL 13

Expert Comment

by:nike_golf
ID: 36530923
Sorry the answer is put in column "D"... and can be adjusted by the x.Offset(0, 3)

NG,

0
 

Author Comment

by:RWayneH
ID: 36531101
Sorry, I forgot to ask....  Is there a way to put a hyphen between the values in Col A and Col B?  -R-
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 13

Accepted Solution

by:
nike_golf earned 500 total points
ID: 36531187
Public Sub concat()
Dim rng, x As Range

Set rng = ActiveSheet.Range("a1", Range("A1").End(xlDown))

For Each x In rng
    x.Offset(0, 3) = x & " - " & Trim(x.Offset(0, 1))
Next x

End Sub

NG,
0
 

Author Closing Comment

by:RWayneH
ID: 36531400
This is an EXCELent and very flexible solution.  Thanks. -R-
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 36531730
Glad I could help.

NG,
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

809 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