Finding a string between other strings

[Guest]

I have a data source linked to Access that contains the company, address,
name, email and phone in one field. I would like to extract the email
address only from this field. Using:

LastName:
IIf(InStr(InStr([body],"email:")+1,[body],"email:")<>0,Right([body],Len([body])-InStr(InStr([body],"email:")+1,[body],"email:")),Right([body],Len([body])-InStr([body],"email:")))

I can isolate the email address but the remaining phone info is still tacked
on to the end. I'd like the query to extract the at the begining of the
email and stop at the end. The email is separated by either a space or
carriage return. Any ideas?

 

[Ken Snell [MVP]]

Lop off the trailing end of the string after you get the email + phone info:

LastName:
Left(IIf(InStr(InStr([body],"email:")+1,[body],"email:")<>0,Right([body],Len([body])-InStr(InStr([body],"email:")+1,[body],"email:")),Right([body],Len([body])-InStr([body],"email:"))),
InStr(IIf(InStr(InStr([body],"email:")+1,[body],"email:")<>0,Right([body],Len([body])-InStr(InStr([body],"email:")+1,[body],"email:")),Right([body],Len([body])-InStr([body],"email:"))),
" ") - 1)