[cpetrey]

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: fake@gmail.com 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.

[Toadill]

Try something like this

intLen = Len("fake@gmail.com")    ' intLen = 14
intEndPosition = intLen + 1
'check for email address
'intNoAt = InStr("fake@gmail.com", "@") = 0
strEmail = "fake@gmail.com"
intAtNum = InStr(strEmail, LCase$("@"))
intAtNum = intAtNum + 1 

'values needed are intAtNum and intEndPosition

[maj3091]

Something like below works. It's VB6, but should be equally adequate for VBA

Dim strText As String
Dim intAt As Integer, intFrontSpace As Integer, intLastSpace As Integer
Dim strOutput As String

    'text1.text held the string you specified
    strText = Text1.Text

    intAt = InStrRev(strText, "@")
    intFrontSpace = InStrRev(strText, " ", intAt)
    intLastSpace = InStr(intAt, strText, " ")
    
    strOutput = Mid(strText, intFrontSpace + 1, (intLastSpace - intFrontSpace) - 1)
    MsgBox strOutput

This post has been edited by maj3091: 02 March 2012 - 10:11 AM

[Toadill]

To begin with your numbers seem to be high for the function and the example that you are using.

Mid$(string, start[, length])

intEndposition = InStr(intAtNum, fake@gmail.com, " ")
strSubstr = Mid$("fake@gmail.com", intAtNum, intEndPosition)

Output = gmail.com

Are you looking for ASCII values?

This post has been edited by Toadill: 02 March 2012 - 11:39 AM

[cpetrey]

I think you are all replying assuming my string is only the email address. My string is the whole phrase which I have posted. I have thousands of open ended responses in a file and I want to pull just the email out of them. The only thing that I can count on being in there is the "@" which is why I have based my whole query on "@". Sorry for the confusion. Any suggestions now?

[maj3091]

Did you actually run my code and try it with a different email address?

It looks for the @ sign, then the first space before and the last space after and grabs everything in between, which you should find is the email address as you requested.

This post has been edited by maj3091: 03 March 2012 - 12:18 AM

[cpetrey]

Sorry when I wrote my response I somehow skimmed over yours. I did run your code, I definitely believe that is the solution but for some reason its only returning "0". I'll have to keep playing with it. Thanks!

maj3091, on 03 March 2012 - 12:17 AM, said:

Did you actually run my code and try it with a different email address?

It looks for the @ sign, then the first space before and the last space after and grabs everything in between, which you should find is the email address as you requested.

[maj3091]

That's OK.

Strange, in "normal" vb, it works well.

Post what you've got now in your spreadsheet and I'll take a look.

[cpetrey]

Well I have to make modifications because I need it to populate adjacent cells. I have thousands of these strings that I need to scrape, thus a message box is not sufficient.

below is my code

[code]

Dim x As Integer
Dim StrText As Integer
Dim IntAT As Integer
Dim IntFrontSpace As Integer
Dim IntBackSpace As Integer

x = 2

Do

StrText = Cells(x, 2)

IntAT = InStrRev(StrText, "@")
IntFrontSpace = InStrRev(StrText, " ", IntAT)

Cells(x, 11).Select
Cells(x, 11).Value = IntFrontSpace

x = x + 1

On Error Resume Next

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

x = 2

Do

IntBackSpace = InStr(IntAT, StrText, " ")

Cells(x, 12).Select
Cells(x, 12).Value = IntBackSpace

x = x + 1

On Error Resume Next

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

maj3091, on 03 March 2012 - 01:38 PM, said:

That's OK.

Strange, in "normal" vb, it works well.

Post what you've got now in your spreadsheet and I'll take a look.

[maj3091]

I appreciate the messagebox wasn't what you wanted, I provided a working example for you to drop into your usage.

Looking at your code, you've declared StrText as an integer, try changing it to a string!