Spltting address with a formila

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
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:


UVWXYZ
Site AddressStreetSuburbPostcodeStatePostal Address
14 Lesson Street Charnwood NSW 250114 Lesson StreetCharnwood2501NSW14 Lesson Street Charnwood NSW 2501
52 Mater Street, Collingwood VIC 306652 Mater StreetCollingwood3066VIC1 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.





KLMNOPQR
StreetSuburbStatePostcodePostal StreetPostal SuburbPostal StatePostal post Code
='Raw'!V2='Raw'!W2='Raw'!Y2='Raw'!X2 XXXX

<tbody>
</tbody>


All help greatly appreciated
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Board Regular
Joined
Sep 9, 2016
Messages
232
Use SEARCH()

Code:
=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

Board Regular
Joined
Jul 12, 2016
Messages
125
FDibbins,

Apologies the 2nd sheet would look like below

UVWXYZ
Site AddressStreetSuburbPostcodeStatePostal Address
14 Lesson Street Charnwood NSW 250114 Lesson StreetCharnwood2501NSW14 Lesson Street Charnwood NSW 2501
52 Mater Street, Collingwood VIC 306652 Mater StreetCollingwood3066VIC1 Jones Street Melbourne VIC 3000

<tbody>
</tbody>
:

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)




KLMNOPQR
StreetSuburbStatePostcodePostal StreetPostal SuburbPostal StatePostal post Code
14 Lesson StreetCharnwoodNSW2501
52 Mater StreetCollingwoodVIC30661 JonesMelbourneVIC3000

<tbody>
</tbody>
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
Nvm.
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
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"

Code:
=RIGHT(A1,4)
Returns: 2501

Code:
=MID(RIGHT(A1,8),1,3)
Returns: NSW

Code:
=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

Board Regular
Joined
Sep 9, 2016
Messages
232
Cell A1 contains: "14 Lesson Street Charnwood NSW 2501"

Code:
=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.
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
Cell A1 contains: "14 Lesson Street Charnwood NSW 2501"
Cell L1 contains: "14 Lesson Street"
Cell N1 contains: "NSW"
Cell O1 contains: "2501"

Code:
=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
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
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

Rich (BB code):
=IF(A1=B1, "", RIGHT(B1,4))

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

etc.
 
Last edited:

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
Modified to include site vs postal address comparison.


data structure:



Code:
' 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.


Code:
' 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))
 

Forum statistics

Threads
1,086,233
Messages
5,388,607
Members
402,126
Latest member
kalcerro_1

Some videos you may like

This Week's Hot Topics

Top