Suppose all your entries are in column "A" from line 1;

Suppose all your entries are in column "A" starting at row 1

Put the following formula in cell "B1":

Place the following formula in cell "B1":

=TRIM(MID(A1;FIND("NAME:";A1)+5;FIND("EMAIL:";A1)-FIND("NAME:";A1)-5))

Copy the formula to the other cells in column "B" and you will not need the macro.

Copy that formula to the remaining cells in column "B" and you're good to go with no macro.

VBobCat, thanks for the help but unfortunately VB did not recognize the "FIND" function. But I found an alternative as you can see below:

Public LastLine As Double

Public i As Double

Public TestString As String

Public TestUpperCase As String

Public TestString2 As Double

Public TestString3 As Double

Sub Click()

On Error Resume Next

    Sheets("Plan2").Select

    LastLine = Range("B" & Rows.Count).End(xlUp).Row

    For i = 1 To LastLine

        TestString = Cells(i, 2).Value
        TestUpperCase = UCase(TestString)
        TestString2 = InStr(TestUpperCase, "NOME:")
        TestString3 = InStr(TestUpperCase, "EMAIL:")
        Cells(i, 3).Value = Mid(TestUpperCase, TestString2, TestString3 - TestString2)


    Next


End Sub

A Regular Expression can be used.

Code

Dim texto As String
Dim objCorresp As Object, objExpReg As Object
Set objExpReg = CreateObject("VBScript.RegExp")
'Expressão Regular
With objExpReg
    .Pattern = "nome:[\s\S]+?(?=e-mail)"
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
End With
texto = "Bla bla bla bla bla NOME: JOSH IDNUMBER: 098766 E-MAIL: [email protected] bla bla bla."
Set objCorresp = objExpReg.Execute(texto)
If objCorresp.Count <> 0 Then
    For Each c In objCorresp
        Debug.Print Trim(c)
    Next c
End If

Result

For the code example, the result is: NOME: JOSH IDNUMBER: 098766

For a multi-line string:

texto = "Bla bla bla bla bla NOME: JOSH IDNUMBER: 098766 E-MAIL: [email protected] bla bla bla." & vbNewLine & _
"Bla bla bla bla bla NOME: JOAO IDNUMBER: 098766 E-MAIL: [email protected] bla bla bla."

The result is:

NOME: JOSH IDNUMBER: 098766
NOME: JOAO IDNUMBER: 098766

Regular Expression

The following expression can be used: nome:[\s\S]+?(?=e-mail)

In this expression it captures text that starts with nome: and is followed by any character with lazy quantizer [\s\S]+? , before e-mail (?=e-mail)

And the demo can be viewed at this link

Enable Regex in Excel

UserDefinedFunction

AUDFcanbecreatedwiththefollowingcode:

Functionextrair_texto_entre(inicioAsString,fimAsString,texto)AsStringDimobjCorrespAsObject,objExpRegAsObjectSetobjExpReg=CreateObject("VBScript.RegExp")
    'Expressão Regular
    With objExpReg
        .Pattern = inicio & "[\s\S]+?(?=" & fim & ")"
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
    End With
    Set objCorresp = objExpReg.Execute(texto)
    If objCorresp.Count <> 0 Then
        For Each c In objCorresp
            extrair_texto_entre = Trim(c)
        Next c
    End If
End Function

Result

This function can be used in the Worksheet as follows:

=extrair_texto_entre("string de início"; "string de fim"; célula ou "string")

In that it can be used or referenced by a cell or by entering a String.

With string

In it, you use the function as follows:

=extrair_texto_entre("nome:";"e-mail";"Bla bla bla bla bla NOME: JOSH IDNUMBER: 098766 E-MAIL: [email protected] bla bla bla.")

And you get the following result: NOME: JOSH IDNUMBER: 098766

Withcell

Inthiswayyouinsertthefunctionintotheworksheetasfollows:

=extrair_texto_entre("nome:";"e-mail";B2)

And you get the result:

NOME: JOSH IDNUMBER: 098766