Hi,

You may try this array formula (Ctrl+Shift+Enter)

=TRIM(LEFT(A7,MAX(IF(ISNUMBER(SEARCH(B1:B165,A7)), SEARCH(B1:B165,A7)))-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Ashish
The only problem is the data in col. B is a list of the suburbs in a
region and your formula relies by chance as one of the suburbs being on
the same line in the sheet
It would need to say if any of the data in col b. = the same as col a.
delete it from col. A

Many thanks
Stephen


"Ashish Mathur" wrote in message
...
Hi,

Assume 15 Smith Street Blacktown is in cell A7. In cell C7, enter the
following formula

=TRIM(LEFT(A7,SEARCH(B7,A7)-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Hi

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