Loop to extract two different strings from a single string in Excel VBA

Question

I received html text in excel and I am just trying to pull out some text from it.

I have the following text in cell A1:

<b>From:</b></p>  </td>  
<td width=760 colspan=10 valign=bottom 
     style='width:380.0pt;padding:0in 0in 0in 0in;  height:9.05pt'>  
<p class=MsoNormal><a href="mailto:name@email.com">LastName, First</a></p>  
</td> </tr>

I want to extract "name@email.com" and "LastName, First" and put each into cells B1 and C1, respectively. I will need to loop this through multiple cells so I would need to consider that strings differ in length.

For more context, this previous thread provided a good foundation for what I am trying to do, but I am stuck on how to proceed since I am pulling strings that would vary in length and content.

Answer 1

Maybe something like that. Beware of quotes ("), my code for the HTML variable has not taken it into account! Find in HTML code string separators that are always the same

Dim HTMLarray1() As String
Dim HTMLarray2() As String
Dim HTML As String

HTML = "<b>From:</b></p></td><td width=760 colspan=10" _
& "valign=bottom style='width:380.0pt;padding:0in 0in 0in 0in;" _
& "height:9.05pt'><p class=MsoNormal>" _
& "<a href="mailto:name@email.com">LastName, First" _
& "</a></p></td></tr>"

HTMLarray1 = Split(HTML, "<a href="mailto:")
HTMLarray1 = Split(HTMLarray1(1), "">")
HTMLarray2 = Split(HTMLarray1(1), "</a>")

Dim email As String
Dim name As String
email = HTMLarray1(0)
name = HTMLarray2(0)

Answer 2

With the string in A1, in B1 enter:

=LEFT(MID(A1,FIND("mailto:",A1)+7,9999),FIND(CHAR(34),MID(A1,FIND("mailto:",A1)+7,9999))-1)

and in C1 enter:

=LEFT(MID(A1,FIND(B1,A1)+LEN(B1)+2,9999),FIND("<",MID(A1,FIND(B1,A1)+LEN(B1)+2,9999))-1)

For example: