Trimming Email addresses

[Guest]

Hi,
I have a column with Email addresses like:
Email <[email protected]>
Email <[email protected]>
nbsp Please send written requests to <sjohnson@usaid>
TO <[email protected].>
(e-mail address removed)
I nee to trim them on both sides so I will have only Email address and
nothing else.
I used this:
Sub trimcells()
For Each cel In Selection
cel.Value = Trim(Right(cel, Len(cel)">"), Left(cel, Len(cel)"<"))
Next cel
End Sub
It doesn't work.
I will appreciate any help.
Thank you.
Oxana.

 

[Tom Ogilvy]

Sub trimcells()
dim iloc as Integer, iloc1 as integer
Dim cel as Range
For Each cel In Selection
iloc = Instr(cel.Value, "<")
iloc1 = Instr(cel.Value,">")
if iloc <> 0 and iloc1 <> 0 and iloc1 > iloc then
cel.Value = Mid(cel.value, iloc+1, iloc1-iloc-1)
end if
Next Cel
End Sub

 

[Guest]

Hi

Should your line of code

cel.Value = Trim(Right(cel, Len(cel)">"), Left(cel, Len(cel)"<"))

be replaced by
OldValu = CStr(Cel.Value)
LeftPOS = INSTR(1,OldValu,"<", vbTextOnly)
RightPOS = INSTR(1,OldValu,">", vbTextOnly)
NewValu = TRIM(MID(OldValu,LeftPOS+1,(RightPOS-LeftPOS)))

or something close to it
regards

 

[Dave Peterson]

Another option:

Select your range
then record a macro when you:

Edit|Replace
what: "*<" (w/o the quotes)
with: (leave empty)
replace all

And once more
edit|replace
what: ">*" (w/o the quotes)
with: (leave blank)
replace all