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.

[Tempted to use a regex?](https://stackoverflow.com/a/1732454/1188513) ..IIRC there's a library you can reference to parse HTML and then pull the specific nodes you need, assuming the cells contain actual valid HTML. — Mathieu Guindon, Mar 14 '19 at 17:23
I was told that the html is not valid, as some of the cells are missing closing tags, etc. But the tags around the text I need will be consistent in each cell, fortunately. — Adriana, Mar 14 '19 at 17:33
So basically you need to pull the `` tag from invalid HTML. And there's only ever one single such tag? — Mathieu Guindon, Mar 14 '19 at 17:36
Yes, to both questions. I would need to pull the A tag, and from there distinguish to pull the text between (mailto:) and (">) to get the email, and (">) and () for the LastName, First — Adriana, Mar 14 '19 at 17:48
Then you could use `InStr` to get the first index of ``; you can then use other standard string-manipulation functions to extract the substrings you need (`Mid`, `InStr`, `Left`, `Trim`). Essentially a code version of what Gary's Student posted as an Excel formula below — Mathieu Guindon, Mar 14 '19 at 18:08
Thank you both for the help! I am working through making a code version, as I will likely have to do a similar task soon. — Adriana, Mar 14 '19 at 18:29

Gary's Student · Accepted Answer · 2019-03-14T18:16:47.803

1

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:

edited Mar 14 '19 at 18:16

answered Mar 14 '19 at 17:37

Gary's Student

95,722
10
59
99

This got the job done, thank you! I am just working through trying to make a code version as Mathieu mentioned above. – Adriana Mar 14 '19 at 18:28

Malamare · Answer 2 · 2019-03-15T09:05:46.353

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)

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

2 Answers2