How do I merge 2 columns

[Deerg65]

Ok so basically its a bit more complicated than that, I want to merge a phone number and home phone number into one column , but I want the home phone number to go to the next line in the merged column which also needs a foreign key to be repeated if the employee has 2 numbers entered. 

Edit: I Managed to do it with a little help

[#2]

@Deerg65

I didn't understand the ID#

You can use the following formula and then click on "Wrap Text" option:

=TEXTJOIN(CHAR(10),,B2:C2) 

[#3 · Accepted solution]

@Deerg65 

Run the following macro:

Sub MergePhone()
    Dim rng As Range
    Application.ScreenUpdating = False
    Set rng = Range("C:C").Find(What:="*", SearchDirection:=xlPrevious)
    Do
        rng.Offset(1).EntireRow.Insert
        rng.Offset(1, -2).Value = rng.Offset(0, -2).Value
        rng.Offset(1, -1).Value = "'" & rng.Value
        Set rng = Range("C:C").Find(What:="*", After:=rng, SearchDirection:=xlPrevious)
        If rng.Row = 1 Then Exit Do
    Loop
    Range("C:C").ClearContents
    Application.ScreenUpdating = True
End Sub

[#4]

@Deerg65 You don't specify what "a foreign key" entails. But perhaps you mean that "04" should be in the beginning of every phone number. If so, try this in D2, set the cell format to wrap text and copy it down:

=TEXT(B2,"0000000000")&CHAR(10)&IF(ISBLANK(C2),"",TEXT(LEFT(B2,1),"00"))&C2

[#5]

Thank You Soo Much!! This is exactly what I needed!!

[#6]

Hey Hans, If I want to merge 6 columns instead of just 2 what would i need to change??

[#7]

@Deerg65 

You've never said "Why" you are doing these merges. In general, merging data like that is something people regret later, since Phone# becomes ambiguous once done....whereas "Cell" and "Home" are clear. I can't imagine why you'd want to merge six, unless you're talking the whole home address, and there too I'd want to ask, "Why?!!"

If you're doing this so as to create a printout of each person's contact info, or a nice address list, you'd be far better served, over the long term, by keeping each data element intact as separate cells and using MailMerge in Word (with the Excel database as the source) to create the printouts.

[#8]

@Deerg65 

See your new discussion How can I merge 6 columns?? 

I posted a macro there.