Removing part(s) of text from downloaded data

[CAT]

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

 

[Mike H]

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

 

[Mike H]

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

 

[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]

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]

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]

Hi,

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

Mike

 

[Ron Rosenfeld]

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

 

[CAT]

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

 

[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

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[CAT]

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

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

 

[Ron Rosenfeld]

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

 

[Ashish Mathur]

Hi,

Is there always a ")" in the string.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com