instr help 1

[lunargirrl]

need urgent help
i am trying to store in a variable the e-mail address of my users, i know the start position, but how can i guess where it ends?

eG I want to extract only the e-mail address of the following data:

mylines:
Johan Anderson Myers EMAIL: johan@email.com Bangkok
Cyndi Lauper EMAIL: cyndi@lauper.com.ar Argentina

my code:

dim Int_Start_Position as integer
Int_Start_Position = InStr(myline, "EMAIL: ")

** ok, now I have the start position, i have to use the function Mid to capture the e-mail address from my string line, but i dont know how to find the final position. **

Thanks in advance,
lunar

 

[EBGreen]

try:
Int_Stop_Position = Instr(Int_Start_Position + 1, myline, " ")

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[cjrobiso]

Without stating the obvious, your InStr() example would need to be modified to find the true beginning of the email address. InStr() finds the beginning of the identified item. Modify the InStr() to:
Int_Start_Position = InStr(myline, "EMAIL: ") + len("EMAIL: ")

You did not mention if all of your email addresses are one continguous word. If they are, you could set the end point of the Mid to the next available empty character " ". For instance:
'Parses the string starting at the email address
strEAdd = Mid(strEAdd, lLoop)
'Parses the string to eliminate anything after the address
strEAdd = Mid(strEAdd, 1, InStr(1, strEAdd, Space(1)) - 1)

 

[DrJavaJoe]

How about something totally different:

Private Sub Command1_Click()
MsgBox ExtractEmail(myline)
End Sub

Private Function ExtractEmail(strValue As String) As String
On Error Resume Next
With CreateObject("VBScript.RegExp")
.Pattern = "([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})"
ExtractEmail = .Execute(strValue)(0)
End With
End Function

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[strongm]

Drat! If you're going to do the RegExp solution I'm going to have to find a different anlternative...hmmm...ah!

MsgBox Split(Split(myLine, "EMAIL: ")(1), " ")(0)

 

[DrJavaJoe]

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[lunargirrl]

Thank you people for the quick answers!!! It workd fine!
Now my last question:

Case 2:
I have the scenario:
Johan Anderson Myers@EMAIL: johan*email.com@Bangkok
Cyndi Lauper@EMAIL: cyndi*lauper.com.ar@Argentina

my separator is the @ now.

Using the code of TomThumbKP (the one I understood easier)

Int_Stop_Position = Instr(Int_Start_Position + 1, myline, "@"), instead of " ", i just placed the "@" and it didnt work. :/

How can I make this work my god!!!!!!!!!!

So I am in the point, that i dont understand this Instr function

 

[CasperTFG]

The Instr function returns the position where the item was found... From MSDN


So to simplify the code...

Dim Int_Start_Position As Integer
Dim Int_Stop_Position As Integer
Dim Int_Long As Integer
Dim Str_Email As String

' This tells me Where in the string it found "EMAIL: " Though I made some modifications in Red to get the starting position of your actual email address.[color]
Int_Start_Position = InStr(myline, "EMAIL: ") + Len("EMAIL: ")

' This tells me Where in the string it found a space " ".
Int_Stop_Position = InStr(Int_Start_Position, myLine, " ")

' This tells me How long the email is End length minus start length.
Int_Long = Int_Stop_Position - Int_Start_Position

Str_Email = Mid(myLine, Int_Start_Position, Int_Long)


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

Sorry I forgot the MSDN wording...

InStr Function

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Oh and strongm, A star for you... I jsut used your split(...)(0) routine... That little thing will come in very usefull...



Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".