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

 

[FDibbins]

I am a little puzzled as to what your 2nd sheet actually looks like, all you have is some formulas shown?
Can you show a few samples of what your expected outcome would look like?

 

[Krayons]

Use SEARCH()

=SEARCH (find_text, within_text, [start_num])

Arguments:
find_text - The text to find.
within_text - The text to search within.
start_num - [optional] Starting position in the text to search. Optional, defaults to 1.

 

[stroffso]

FDibbins,

Apologies the 2nd sheet would look like below

[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]
:

1. Site and Postal address in first row is the same therefore O-R is left blank
2. Site and Postal address in 2nd row is Different therefore O-R needs formula to pick up address from raw sheet (above)




[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]14 Lesson Street[/TD]
[TD]Charnwood[/TD]
[TD]NSW[/TD]
[TD]2501[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]52 Mater Street[/TD]
[TD]Collingwood[/TD]
[TD]VIC[/TD]
[TD]3066[/TD]
[TD]1 Jones[/TD]
[TD]Melbourne[/TD]
[TD]VIC[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Krayons]

Nvm.

 

[Krayons]

Your question is a difficult one for one reason: a lack of delimiters. The only delimiter found within the Postal Address, is a space character. Because of this, it would be difficult to determine when the street stops, and the postal suburb begins. While you would think this is simple, I will ask you this question: what happens if your street name is two separate words? EG: 13 Candy Cane Street as opposed to simply 13 CandyCane Street.

You could achieve this, but it would require a knowledge of (what appears to be Australia) which I do not have. You could try and separate the two by looking for an array of possible street endings. So for example, searching that postal address for something like "street", "road" or "avenue" (etc), then just spitting out whatever comes before that.

Finding the postal code and the postal state is pretty easy if you search backwards from the postal address, as I assume those are constants so far as the number of characters are concerned.

For example:

Cell A1 contains: "14 Lesson Street Charnwood NSW 2501"

=RIGHT(A1,4)

Returns: 2501

=MID(RIGHT(A1,8),1,3)

Returns: NSW

=MID(F2,1,SEARCH("Street",F2)-2)

Returns: 14 Lesson

You can pretty much accomplish most of what you need by utilizing the various text and string functions within excel. Refer here for help.

 

[Krayons]

Cell A1 contains: "14 Lesson Street Charnwood NSW 2501"

=MID(A1,1,SEARCH("Street",A1)-1+LEN("Street"))

Returns: 14 Lesson Street

The best way I can sum it up, use a combination of the text and string functions, using the code I posted as very basic examples, to search backwards and forwards in each string in order to count characters necessary to produce the results you're looking for.

Edit: Oh, and you're probably going to have to make use of the TRIM() function in the event a postal address contains an additional space character at the end, or something.

 

[Krayons]

Cell A1 contains: "14 Lesson Street Charnwood NSW 2501"
Cell L1 contains: "14 Lesson Street"
Cell N1 contains: "NSW"
Cell O1 contains: "2501"

=MID(A1,LEN(L1)+2,LEN(A1)-(LEN(L1)+LEN(N1)+LEN(O1)+2))

Result: Charnwood

Here, fiddle with this example workbook: https://1drv.ms/x/s!AnpELGec0aCqlcF68chyKAejkMu5OQ

 

[Krayons]

As for the site address vs postal address check, you can accomplish that by wrapping each formula in an if statement like:

cell A1 = site address
cell B1 = postal address

=IF(A1=B1, "", RIGHT(B1,4))

=IF(A1=B1, "", MID(RIGHT(B1,8),1,3)))

etc.

 

[Krayons]

Modified to include site vs postal address comparison.


data structure:

' postal street
=IF(A2=F2, "", MID(F2,1,SEARCH("Street",F2)-1+LEN("Street")))


' postal suburb
=IF(A2=F2, "", MID(F2,LEN(L2)+2,LEN(F2)-(LEN(L2)+LEN(N2)+LEN(O2)+2)))


' postal state
=IF(A2=F2, "", MID(RIGHT(F2,8),1,3))


' postal post code
=IF(A2=F2, "", RIGHT(F2,4))

modified to strip the "," characters (code: 44) from the postal address.

' postal street
=IF(A2=F2, "", MID(TRIM(SUBSTITUTE(F2,CHAR(44),"")),1,SEARCH("Street",TRIM(SUBSTITUTE(F2,CHAR(44),"")))-1+LEN("Street")))


' postal suburb
=IF(A2=F2, "", MID(TRIM(SUBSTITUTE(F2,CHAR(44),"")),LEN(L2)+2,LEN(TRIM(SUBSTITUTE(F2,CHAR(44),"")))-(LEN(L2)+LEN(N2)+LEN(O2)+2)))


' postal state
=IF(A2=F2, "", MID(RIGHT(TRIM(SUBSTITUTE(F2,CHAR(44),"")),8),1,3))


' postal post code
=IF(A2=F2, "", RIGHT(TRIM(SUBSTITUTE(F2,CHAR(44),"")),4))