Lightly tested, this should work ok ..
The reference suburbs in B1:B165 are assumed fully populated
With your source addresses in A1 down,
Put this in C1, normal ENTER to confirm will do:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$165,A1)))0,TRIM(SUBSTITUT E(A1,INDEX($B$1:$B$165,MATCH(TRUE,INDEX(ISNUMBER(S EARCH($B$1:$B$165,A1)),),0)),"")),"")
Copy C1 down to the last row of source data in col A. Col C should return
the desired cleansed results. Success? wave it here ..
--
Max
Singapore

"Father John" wrote in message
...

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove
from col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen

A slight correction to the end part of the expression. It should read:
.... )),),0)),"")),A1)

Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)

Stephen,

just replace the last "" with A1.

Max,

that's pretty neat! I particularly like the INDEX(array,) wrapping to
elimate the need for array entry. Definitely something I'll be putting to
good use. Cheers.

Steve D.



"Father John" wrote in message
...
Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)

Taadaa
It works exactly right
Many thanks
Stephen


"Steve Dunn" wrote in message
...
Stephen,

just replace the last "" with A1.

Max,

that's pretty neat! I particularly like the INDEX(array,) wrapping to
elimate the need for array entry. Definitely something I'll be putting to
good use. Cheers.

Steve D.



"Father John" wrote in message
...
Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be
able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)

welcome, good to hear
--
Max
Singapore

"Father John" wrote in message
...
Taadaa
It works exactly right
Many thanks
Stephen