Spltting address with a formila

[stroffso]

Hi,

I need to split some addresses with formulas and am not sure the right way of going about doing it.

I currently have two workbooks. One called Raw Data which has a series of columns and the other called master which looks up the raw tab.

On the raw tab there are 6 columns that are like so:

U	V	W	X	Y	Z
Site Address	Street	Suburb	Postcode	State	Postal Address
14 Lesson Street Charnwood NSW 2501	14 Lesson Street	Charnwood	2501	NSW	14 Lesson Street Charnwood NSW 2501
52 Mater Street, Collingwood VIC 3066	52 Mater Street	Collingwood	3066	VIC	1 Jones Street, Melbourne VIC 3000

<tbody>
</tbody>

What i need is for the master tab to look these up in a certain way and return data to the cells marked X in the table below using the following rules:


1. If the postal address is the same as the site address then "" needs to be returned.
2. If the postal address is different then the address in Column Z above needs to be split across Columns O-R below

There are no commas separating the addresses so this where I have the problem really.

K	L	M	N	O	P	Q	R
Street	Suburb	State	Postcode	Postal Street	Postal Suburb	Postal State	Postal post Code
='Raw'!V2	='Raw'!W2	='Raw'!Y2	='Raw'!X2	X	X	X	X

<tbody>
</tbody>

All help greatly appreciated

 

[Krayons]

How would I go about adding some array goodness into the mix?

SEARCH("Street", blah blah)

Something like (?):

SEARCH({"Street", "Road", "Avenue", "Boulevard", "Lane", "Cresent"}, blah blah)

Thanks.

 

[stroffso]

How would I go about adding some array goodness into the mix?

SEARCH("Street", blah blah)

Something like (?):

SEARCH({"Street", "Road", "Avenue", "Boulevard", "Lane", "Cresent"}, blah blah)

Thanks.

That awesome thank you!

 

[Krayons]

Wait, that array stuff actually works? My previous post was not a statement, rather it was a question.

Please advise.

 

[stroffso]

Wait, that array stuff actually works? My previous post was not a statement, rather it was a question.

Please advise.

Sorry I was referring to your earlier post detailing what was needed. thanks very much for your expertise and courtesy