Forum Discussion

Copper Contributor

Sep 06, 2021

Solved

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 colu...

Number Task.xlsx24 KB

BI & Data Analysis

excel

Formulas and Functions

Office Scripts

HansVogelaar

Sep 06, 2021

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

Riny_van_Eekelen

Platinum Contributor

Sep 06, 2021

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