How to build formula to check address structure?

[darrenmccoy]

Hi, Could you guys help me with what type of formula would I use to check address formatting and return a true or false?

An example case would be the format of address must be (number, street names, location, state zip) with the commas and spaces.

ID	FULL ADDRESS	VALIDATION
611762	101 SMITHS LANE, PEARCEDALE, VIC 3912	TRUE
633090	1,2,3 RIVER STREET, SOUTH YARRA, VIC 3141	FALSE
633098	1A MATTHEWS COURT, TOORAK, VIC 3142	TRUE
633100	43/10 CAPISTRANO PLACE, MELBOURNE	FALSE
633102	1/17 WELL STREET, BRIGHTON, VIC 3186	TRUE
633103	1&2 DOW STREET, MELBOURNE, VIC 3207	FALSE
633126	74 WITHERS STREET, ALBERT PARK, VIC 3206	TRUE
633129	CAMP STREET, CHELSEA, VIC 3196	FALSE
633130	33 BODLEY STREET BEAUMARIS VIC 3193	FALSE
633131	14 NORTH ROAD, BRIGHTON, VIC 3186	TRUE

I realise there are a heap of variables that may exist, is there a base checking formula I can use to determine if the correct format exists?

Thanks

 

[Peter_SSs]

I would use vba to try to do this. Is that acceptable?

Can you clarify the 'rules' for 'number'.
For example, I presume 633103 fails due to 1&2 for 'number'?
Would 1-2 DOW STREET be acceptable?
What about these
3-5/10 DOW STREET?
UNIT 7 DOW STREET?

Are all texts in upper case like you samples?

 

[darrenmccoy]

I would use vba to try to do this. Is that acceptable?

-- Thanks, I do not know how to write VBA, but I can add it if I have the script. Generally if I can run a formula I will because often I use gSuite as well for different dataset ---

Can you clarify the 'rules' for 'number'.
For example, I presume 633103 fails due to 1&2 for 'number'? -- YES ---
Would 1-2 DOW STREET be acceptable? -- YES ---
What about these
3-5/10 DOW STREET? -- YES ---
UNIT 7 DOW STREET? -- NO, that would fail ---

Are all texts in upper case like you samples? - -- YES ---

 

[Peter_SSs]

I would be happy for somebody to prove me wrong but I don't think this is feasible with standard worksheet functions. However, this user-defined function is pretty short & the instructions to set it up are ...

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you may use the icon at the top right of the code pane) into the main right hand pane that opens at step 2
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

If the function does not work for some cases, please provide details & examples.

Function IsValid(s As String) As Boolean
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^\d[^& \,]* )([A-Z\- ']+\, ){2}(QLD|NSW|VIC|TAS|SA|WA|NT|ACT)( \d{4}$)"
  IsValid = RX.Test(s)
End Function

darrenmccoy 2020-03-14 1.xlsm
	A	B	C
1	ID	FULL ADDRESS	VALIDATION
2	611762	101 SMITHS LANE, PEARCEDALE, VIC 3912	TRUE
3	633090	1,2,3 RIVER STREET, SOUTH YARRA, VIC 3141	FALSE
4	633098	1A MATTHEWS COURT, TOORAK, VIC 3142	TRUE
5	633100	43/10 CAPISTRANO PLACE, MELBOURNE	FALSE
6	633102	1/17 WELL STREET, BRIGHTON, VIC 3186	TRUE
7	633103	1&2 DOW STREET, MELBOURNE, VIC 3207	FALSE
8	633126	74 WITHERS STREET, ALBERT PARK, VIC 3206	TRUE
9	633129	CAMP STREET, CHELSEA, VIC 3196	FALSE
10	633130	33 BODLEY STREET BEAUMARIS VIC 3193	FALSE
11	633131	14 NORTH ROAD, BRIGHTON, VIC 3186	TRUE
12	633103	1-2 DOW STREET, MELBOURNE, VIC 3207	TRUE
13	633103	3-5/10 DOW STREET, MELBOURNE, VIC 3207	TRUE
14	633103	UNIT 7 DOW STREET, MELBOURNE, VIC 3207	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IsValid(B2)

 

[darrenmccoy]

Wow, That's so simple and amazing!
Thank you so much!
Screenshot of it in action and working:

 

[darrenmccoy]

Peter, How do I save that function so as to be able to use it in any spreadsheet?

 

[Peter_SSs]

Wow, That's so simple and amazing!
Thank you so much!
Screenshot of it in action and working:

You're welcome. Glad it worked for you.

How do I save that function so as to be able to use it in any spreadsheet?

If you open the vba window again (Alt+F11 is one way) do you see PERSONAL.XLSB in the Project Explorer pane at the left?

If so, select it then Insert -> Module as we did before and past the function code in that new module & Save
If not, post back & I'll give further instruction.

 

[Peter_SSs]

If not, post back & I'll give further instruction.

Actually, if you don't have a PERSONAL.XLSB, see here for the steps to make one.