Hi,

Is there always a ")" in the string.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CAT" wrote in message
...
Hi Mike,

For ex:
In Cell B2:
Kirriemuir Barratt Moss (IRE) Address Telephone Number

I want to end up with:
Kirriemuir Barratt Moss (IRE)

and eliminate Address and Telephone Number from Cell B2

In Cell B3, I might have:
Bridge Oldrik (GER) Telephone Number

I want to end up with:
Bridge Oldrik (GER)

and eliminate Telephone Number from cell B3

and so on; hope it makes sense.
CAT



"Mike H" wrote:

Hi,

Post some sample data and the result you expect to get from that data

Mike

"CAT" wrote:

Hi Mike,
Apologies, my info was not quite correct:
First of all the column with the data is col D; secondly, there are
more
than one name before the text to be removed, sometimes two and up to
five
names (just checked) with no punctuation but just spaces.
I have entered your formula in cell d1 and copied and pasted it down
col D
and it erased everything, returning me 0 in the right hand corner of
each
cell!

As you can ascertain by this, I am a newbie; I use Excell 2007 by the
way.
Thank you for your help
CAT

"Mike H" wrote:

Hi,

If it's just a surname followed by a space then this works. With your
data
in column A starting in a1 put this in b1 and drag down

=IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1)))

Mike

"CAT" wrote:

Hi Mike,

Just surnames, no commas and the words "address" or "telephone
number" or
"address and tel nr";
Mike, do I run your formula first for removing the word "address",
then run
it again changing "telephone number" for "address" in the formula,
and so on
for the 3rd deletion?
Thank you

"Mike H" wrote:

Hi,

On reflection if the cell eith contains just a name or a name and
the word
address then this should work

=IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1)))

Mike

"Mike H" wrote:

Hi,

Can you give some example of the names?

Are they surnames only?
Forenames & Surnames?
A middle initial/name?

or combinations of the above?

Mike

"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