You can use TRIM to get rid of spaces. Also, try Chr(10) and CHR(160).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


<Andy wrote in message ...
Thanks Bob. I 'm most grateful for your help! I am still left with a lot
of email addresses with space at either end and with preceeding/trailling
'squares'. I think they are carriage returns and have tried replacing
chr(13) and chr(9) with macros, but have made no progress.
Cheers anyway.
Andy.

"Bob Phillips" wrote in message
...
Try this array formula

=MID(A1,MAX(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))) ,1)="
")*(ROW(INDIRECT("1:"&LEN(A1)))<FIND("@",A1)),ROW( INDIRECT("1:"&LEN(A1)))))+1,
FIND(" ",A1&"
",FIND("@",A1))-1-MAX(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*(ROW(INDIRECT("1:"&LEN(A1)))<FIND("@",A1)),ROW( INDIRECT("1:"&LEN(A1))))))

as an array formula, it is committed with Ctrl-Shift-Enter, not just
Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


<Andy wrote in message ...
Hi

I have been looking for an answer to this for weeks - and I admit
defeat!
I have a column of data with email addresses in there, somewhere. Each
email is preceeded and followed by a space. There are also non-printing
'squares' in there - but I'm not sure if that makes a difference or not!
Sample text could be
This message has been returned .. . . address was
and this address . . . . . etc

So I am trying to extract the section of text with the @ sign, up to but
excluding the spaces at both ends.
Help me, please!
Andy.