How to delete some text in-between a string in a c

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: [phone]
Main Fax: [phone]
Other Phone: [phone]

dfr
China
Main Phone: [phone]
Main Fax: [phone]

rtg
United States
Main Phone: [phone]
Main Fax: [phone]
Other Phone: [phone]

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