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

HansVogelaar

MVP

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

Deerg65

Copper Contributor

Sep 06, 2021

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

Forum Discussion

How do I merge 2 columns

Resources