• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

Join to Excel cells using a macro

Hello Experts Exchange
I had a interview test and one of the questions was to combine two Excel cells one called forename the other called surname in to one cell using a macro.  Is this possible to do using a macro and if so can you tell me the steps on how to make the macro work please?


  • 2
1 Solution
Martin LissOlder than dirtCommented:
Record a macro while doing it manually and you will have the macro you need.
Are you sure it was a macro and not a formula?  I have written that formula many times.
I could see a macro creating a third column with forename and surname being concatenated together and then removing the two original columns.

This code assumes two columns Forename and SurName.  It will create a label in the third column and then loop thru the names while putting the forename and surname in the third column.
Public Sub FullName()
ActiveCell.Offset(0, 2).Value = "Full Name"
ActiveCell.Offset(1, 0).Activate
  ActiveCell.Offset(0, 2).Value = ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)
End Sub

Open in new window

Saqib Husain, SyedEngineerCommented:
You could use something like this

Sub combine1st2nd()
    Dim cel As Range
    For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
        cel.Value = cel.Value & " " & cel.Offset(, 1).Value
        cel.Offset(, 1).ClearContents
    Next cel
End Sub
Martin LissOlder than dirtCommented:
Did any of the above help you?
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

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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