vba - How to delete some text in-between a string in a cell in excel

Question

I have some company addresses in an excel - here is how one of the cells look like. The number of company addresses in any cell varies.

abc
United Kingdom
Main Phone: 1234567
Main Fax: 63273818
Other Phone: 53177188

dfr
China
Main Phone: 2345671
Main Fax: 632738188

rtg
United States
Main Phone: 2434571
Main Fax: 3278188
Other Phone: 31771988

What I want as a outcome is 

abc
United Kingdom

dfr
China

rtg
United States

I have researched for this; however, could not find anything. I am trying to find a way to get the position of "Main" and the empty line and then delete the text in between. I tried with CHAR(10) & CHAR(13)..but those did not work. Any help in this regard would be welcome. Many thanks.

P.S>> If anyone can suggest any VBA code to do work, that would be also useful. Many thanks.

Answer 1

Use this Function:

Function remo(txt As String) As String

    txt1 = Split(txt, Chr(10) & Chr(10))

    For i = LBound(txt1) To UBound(txt1)

        remo = remo & Left(txt1(i), InStr(1, txt1(i), "Main") - 1) & Chr(10) & Chr(10)
        Debug.Print txt2

    Next

End Function

Here you will find how to add a Custom Function In Excel