Hi,
I have a column with Email addresses like:
Email
Email
nbsp Please send written requests to <sjohnson@usaid
TO .

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.

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

--
Regards,
Tom Ogilvy


"OxanaB" wrote in message
...
Hi,
I have a column with Email addresses like:
Email
Email
nbsp Please send written requests to <sjohnson@usaid
TO .

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.

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
"OxanaB" wrote:

Hi,
I have a column with Email addresses like:
Email
Email
nbsp Please send written requests to <sjohnson@usaid
TO .

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.