Hi Ron,
Thank you. I have never used VBA (although I have a book about it); I will
try and follow your instructions in a new book: copy and paste my downloaded
lists in it and see what happens. Just in case I do it all wrong, how can I
get rid of a VBA Code and start again? I know it's not as simple as clearing
a formula from a worksheet.

Thank you again
CAT

"Ron Rosenfeld" wrote:

On Wed, 10 Dec 2008 09:21:01 -0800, CAT wrote:

Hi everyone,

I would be grateful for a little help here with a formula to remove parts of
text in a column containing downloaded data.
In colomn B, with around 300 cells, I have a name, then the words "address"
and/or "telephone number" or "address and tel nr" (no punctuations marks);
those words do NOTappear in every cell: in some cases only the name is
downloaded.

I would like to use a formula to "clean up" the data in this column: to find
and delete those words and be left with only the name (which is of course
different in each cell).
I have used conditional formatting to highlight those bits of text and
deleted them manually but it's taking me all day, there must be an easier way.

Thank you in advance for your help
CAT

Here is a VBA solution with a User Defined Function.

<alt-F11 opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

If your data starts in, let us say, B2, enter the following formula into some
cell:

=RegexSub(B2,"[\s\W]+(Address|Telephone|Tel Nr)[\s\S]+","")

and fill down as far as required.

This can be easily modified in case you have missed something in your
description.

================================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, ReplWith As String) _
As String
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
============================
--ron