Formula to remove text from an address in a cell

[sobrien1234]

I have a list of addresses which are all different in length. In column A I have the full address and in column B I have half the address (i.e. town/suburb and postcode). I need a formula to delete all the text in column B from the text in column A. Example below:
Column A Column B
444 David Street ALBURY NSW 2640 ALBURY NSW 2640
Suite 105, 350 George Street SYDNEY NSW 2000 SYDNEY NSW 2000
Shop 1A 24 Belgrave Street KOGARAH NSW 2217 KOGARAH NSW 2217


If formula works the answers in column C would be:
444 David Street
Suite 105, 350 George Street
Shop 1A 24 Belgrave Street

Thanks In advance!!

 

[63falcondude]

=LEFT(A1,FIND("Street",A1)+5)

Works for your example. This will not work if you have Drive, Road, etc. instead of street.

 

[Jonmo1]

Perhaps in C2

=TRIM(SUBSTITUTE(A2,B2,""))

 

[Oeldere]

Book1
	A	B
25	444 David Street ALBURY NSW 2640 ALBURY NSW 2640	444 David Street
26	Suite 105, 350 George Street SYDNEY NSW 2000 SYDNEY NSW 2000	Suite 105, 350 George Street
27	Shop 1A 24 Belgrave Street KOGARAH NSW 2217 KOGARAH NSW 2217	Shop 1A 24 Belgrave Street
sheet1

Cell Formulas
Range	Formula
B25	=LEFT(A25,SEARCH("street",A25,1)+6)

 

[sobrien1234]

Sorry some are roads, or avenues etc - any other ideas?

 

[sobrien1234]

Thanks but you are right - doesn't work for roads and avenues etc. and tem on the here's plenty of them on the list

 

[sobrien1234]

Sorry that doesn't appear to work. Thanks for trying!!

 

[Jonmo1]

It works great for me, in what way does it not work for you?

Going off of your original description

In column A I have the full address and in column B I have half the address (i.e. town/suburb and postcode). I need a formula to delete all the text in column B from the text in column A

Unknown
	A	B	C
1	Full Address	Half Address	Formula
2	444 David Street ALBURY NSW 2640	ALBURY NSW 2640	444 David Street
3	Suite 105, 350 George Street SYDNEY NSW 2000	SYDNEY NSW 2000	Suite 105, 350 George Street
4	Shop 1A 24 Belgrave Street KOGARAH NSW 2217	KOGARAH NSW 2217	Shop 1A 24 Belgrave Street
Sheet1

Cell Formulas
Range	Formula
C2	=TRIM(SUBSTITUTE(A2,B2,""))
C3	=TRIM(SUBSTITUTE(A3,B3,""))
C4	=TRIM(SUBSTITUTE(A4,B4,""))

 

[sobrien1234]

fantastic!! Sorry for confusion - there was an extra space between the state and the postcode in column B but I have deleted it and it works great. Thanks a million!!

 

[Jonmo1]

You're welcome.