Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Concatinate and Trim Column A and B

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
RWayneH
Asked:
RWayneH
  • 4
  • 2
1 Solution
 
nike_golfCommented:
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
 
nike_golfCommented:
Sorry the answer is put in column "D"... and can be adjusted by the x.Offset(0, 3)

NG,

0
 
RWayneHAuthor Commented:
Sorry, I forgot to ask....  Is there a way to put a hyphen between the values in Col A and Col B?  -R-
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nike_golfCommented:
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
 
RWayneHAuthor Commented:
This is an EXCELent and very flexible solution.  Thanks. -R-
0
 
nike_golfCommented:
Glad I could help.

NG,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now