Text function Query

[vksharma00001]

Hi Experts

I have a data like this:

Killians Self Retaining Nasal Speculum

Suspension Laryngoscope

Tonsillar Snare-Eves

Micro Scissor Scissors

<colgroup><col></colgroup><tbody>
</tbody>

I want a dynamic formula in excel so that i can extract 1st and 2nd word. Special character can be anything like this "-"," ","@",# etc.

I want Output like this:

Killians Self

Suspension Laryngoscope

Tonsillar Snare

Micro Scissor

<colgroup><col></colgroup><tbody>
</tbody>

 

[István Hirsch]

Copy this into an empty Module then try the UDF:

Function ExtractFirstTwoWords(c As String) As String
    With CreateObject("VBScript.RegExp")
        .ignorecase = True
        .Global = True
        .Pattern = "^([a-z]+[ ][a-z]+)|.+?"
        If .Test(c) Then ExtractFirstTwoWords = .Replace(c, "$1")
    End With
End Function

 

[azumi]

=trim(left(substitute(substitute(substitute(substitute(substitute(substitute(substitute(a1,"&"," "),"*"," "),"_"," "),"#"," "),"@"," "),"-"," ")," ",rept(" ",100)),200))