InStrRev() problems

Question

I am trying to write a Do Loop that enters into a long string and returns the place of the space before an email address and the place of the space after. Using InStr() I have been able to locate the end of the email address. Now I need to locate the beginning to then use Mid() to pull the address out. I see that InStrRev() should start at the end of a string and then search, but looking at actual manuals it appears it just gives the second instance of a character. For example:

My string is:

please shoot me an email. My E-Mail: [email protected] If you cannot make it call me.

What I have done so far is returned the place of the @ , which in this case if 42. Then I used InStr() to return the place of the first " " after the @. Which in this case is, 52. I wish to return the place of the first " " BEFORE the @. In this case it should be 37. My plan then is to use Mid(37, 15). Fifteen being the difference of 52 & 37. I have tried using InStrRev() to return 37 but cannot get it to work. Any suggestions? below is my code.

x = 2

Do

Cells(x, 11).Select
Cells(x, 11).Value = (InStrRev(Cells(x, 9), Cells(x, 2), " "))

x = x + 1

On Error Resume Next

Loop Until Cells(x, 2).Value = ""

Where (x,9) is the place of the @ and (x, 2) is the string.

Answer 1

How about:

MyArray = Split(Mystring," ")

For i=0 To Ubound(MyArray)
   If Instr(MyArray(i)),"@")>0 Then
       ''Email
   End If
Next

Answer 2

' Find the @ symbol
Dim atPosition As Integer
atPosition = InStr(cellValue, "@")
' check if found here

' Find the space after the @
Dim secondSpacePosition As Integer
secondSpacePosition = InStr(atPosition, cellValue, " ")
' check if found here

' Find the space before the @
Dim firstSpacePosition As Integer
firstSpacePosition = InstrRev(cellValue, " ", atPosition) '  beware, the arguments differ a little
' check if found here

Dim email As String
email = Mid(cellvalue, firstSpacePosition + 1, secondSpacePosition - firstSpacePosition - 1)

Answer 3

or maybe if all you need is the email address:

Function GetEmail(longstr As String) As String

GetEmail = Filter(Split(longstr, " "), "@")(0)

End Function

Generally, looping should be avoided in Excel as it is slow, the below will do what your code does without the loop:

Columns(12).Cells(1).Resize(Columns(11).Cells(2).End(xlDown).Row - 1, 1).Offset(1).Value = _
            Application.Transpose(Filter(Split(Join(Application.Transpose(Columns(11).Value), " "), " "), "@"))