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

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

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

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

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

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

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

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

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

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

On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote:

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

You can just delete everything in the module that you created (in the VB
Editor).

Or you can right click on the module name in the Project Explorer window (it'll
probably be Module1), and select "Remove module" from the drop-down list. When
it asks if you want to save it first, select No.
--ron

On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote:

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


CAT,

Looking at the examples you posted to Mike H, you will need to change the
function I posted to:

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

--ron

Hi Ron,

I will try and use your code tomorrow. I suppose I better close this query
now (wether or not I can make it work!)
Again thank you for your help
CAT

"Ron Rosenfeld" wrote:

On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote:

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


CAT,

Looking at the examples you posted to Mike H, you will need to change the
function I posted to:

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

--ron

On Wed, 10 Dec 2008 17:36:07 -0800, CAT wrote:

Hi Ron,

I will try and use your code tomorrow. I suppose I better close this query
now (wether or not I can make it work!)
Again thank you for your help
CAT

Please post back in this thread and let us know how you're doing. No need to
"close the query".
--ron

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