Tidy up Addresses- VBA

[Noni]

Hi, how can below codes can be modified to clean the addresses? Suburb name and State names have unwanted spaces and messy.

WorksheetDummy.xlsm
	A	B
1	Raw Address	Cleaned Address
2	1 Co Street WALLA WAL LA NS W	1 Co Street WALLA WALLA NSW
3	Lock Cres MILB AC T	Lock Cres MILB ACT
4	cnr March St/June Sts HENTY N SW	cnr March St/June Sts HENTY NSW
5	4 Apra Street ALBURYQ LD	4 apra Street ALBURY QLD
6	Cnr Bu St & Ur Road LA VINGTON N T	Cnr Bu St & Ur Road LAVINGTON NT
7	May Road LAVINGTON NSW	May Road LAVINGTON NSW
8	1 Octo Street JIND ERA W A	1 OctoStreet JINDERA WA
9	4 Nov Road JINDERA S A	4 NovRoad JINDERA SA
10	7 April Court BURRUM CK T AS	7 April Court BURRUMCK TAS
Sheet3

Function TidySpaces(s As String) As String
  Dim RX As Object
  Dim Pat As Variant
 
  Const Patterns As String = "([a-z])( )([a-z])#(\d)( )(\d)#(\/)( )(.)#([A-Z])( )([A-Z])#([A-Z])( )([a-z])"
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  For Each Pat In Split(Patterns, "#")
    RX.Pattern = Pat
    s = RX.Replace(s, "$1$3")
  Next Pat
  TidySpaces = s
End Function

 

[rlv01]

'Cleaning' usually means a set of rules to decide which part of a string is to be change, and which part is to be left alone. What are your rules for deciding when a space is retained, and when it is removed? For example, take this raw address: 1 Co Street WALLA WAL LA NS W. How is the code to know that the space between "WAL" and "LA" is to be removed, but the space between "Street" and "WALLA" should be retained? What is your rule?

 

[rlv01]

'Cleaning' usually means a set of rules to decide which part of a string is to be changed, and which part is to be left alone. What are your rules for deciding when a space is retained, and when it is removed? For example, take this raw address: 1 Co Street WALLA WAL LA NS W. How is the code to know that the space between "WAL" and "LA" is to be removed, but the space between "Street" and "WALLA" should be retained? What is your rule?

 

[Peter_SSs]

As a further example, NSW has a town "GIN GIN" while WA has "GINGIN". Without a complete list of names and states to compare against, there would be no way to decide if the space in "GIN GIN" should be removed or not.

 

[Noni]

As a further example, NSW has a town "GIN GIN" while WA has "GINGIN". Without a complete list of names and states to compare against, there would be no way to decide if the space in "GIN GIN" should be removed or not.

Name of states/ territories are NSW, WA, SA, ACT, TAS, NT, VIC. Addresses are endless and may be different every year. So I think a rule can be created only on state/ territories.

 

[Peter_SSs]

Name of states/ territories are NSW, WA, SA, ACT, TAS, NT, VIC.

.. and QLD??

So I think a rule can be created only on state/ territories.

The issue is: What is the rule?
As I said before, without a complete list of every possible location in every state and territory, there is no way to know whether to remove a space from the location name or not.
For another example Is "GREEN HILLS" correct as it is or could it be "GREENHILLS"? More importantly, how is Excel to know which one is correct?

 

[Georgiboy]

Another option if you have access to a 'Google API key' could be to pass your current address through googles geocoding service and then output the FullAddress part of the return. This way you would have the addresses as google sees them. Like I said just an option if you have a Google API key.

 

[Kerryx]

Is there any chance that when extracting ( sourcing) the raw addresses(into excel) that some form of delimitation was applied, it would be of interest to see what the source data was before importing to excel if that is possible. Just seems strange that the states and cities are the ones being split abnormally