Forum Discussion
How do I merge 2 columns
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
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
7 Replies
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
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- Hey Hans, If I want to merge 6 columns instead of just 2 what would i need to change??
- Thank You Soo Much!! This is exactly what I needed!!
I didn't understand the ID#
You can use the following formula and then click on "Wrap Text" option:
=TEXTJOIN(CHAR(10),,B2:C2)
