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:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"] U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[/TR]
[TR]
[TD]Site Address[/TD]
[TD]Street[/TD]
[TD]Suburb[/TD]
[TD]Postcode[/TD]
[TD]State[/TD]
[TD]Postal Address[/TD]
[/TR]
[TR]
[TD]14 Lesson Street Charnwood NSW 2501[/TD]
[TD]14 Lesson Street[/TD]
[TD]Charnwood[/TD]
[TD]2501[/TD]
[TD]NSW[/TD]
[TD]14 Lesson Street Charnwood NSW 2501[/TD]
[/TR]
[TR]
[TD]52 Mater Street, Collingwood VIC 3066[/TD]
[TD]52 Mater Street[/TD]
[TD]Collingwood[/TD]
[TD]3066[/TD]
[TD]VIC[/TD]
[TD]1 Jones Street, Melbourne VIC 3000[/TD]
[/TR]
</tbody>[/TABLE]

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.





[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD]Street[/TD]
[TD]Suburb[/TD]
[TD]State[/TD]
[TD]Postcode[/TD]
[TD]Postal Street[/TD]
[TD]Postal Suburb[/TD]
[TD]Postal State[/TD]
[TD]Postal post Code[/TD]
[/TR]
[TR]
[TD]='Raw'!V2[/TD]
[TD]='Raw'!W2[/TD]
[TD]='Raw'!Y2[/TD]
[TD]='Raw'!X2[/TD]
[TD="align: center"] X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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