Search string or substring from range

I have list of scientific names in Sheet 2, range A1:A100. In my textbox, if I enter input information such as "Gray wolf (Canis lupus) is native to North America and Eurasia.", I want to output "Gray wolf (<scientific_name>Canis lupus</scientific name>) is native to North America and Eurasia." assuming that Canis lupus is listed anywhere in A1:A100.

Any help would be greatly appreciated.

try this:

Code:

---

Sub test()
Dim intxt As String


intxt = ActiveCell.Value
lb = InStr(intxt, "(")
rb = InStr(intxt, ")")
If lb > 0 And rb > 0 Then
 sname = Mid(inxtx, lb, rb - lb)
 
 
With Worksheets("Sheet2")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
listar = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 2 To lastrow
 If sname = listar(i, 1) Then
  newstr = Replace(intxt, "(", "(<scientific_name>", 1, 1)
  newstr = Replace(newstr, ")", "</scientific name>)", 1, 1)
  Exit For
 End If
Next i
End With
ActiveCell.Value = newstr
End If
End Sub

---