CODEWITHSUNDEEP
vbagisarcmap
Daniel Sutton
Daniel Sutton

Reputation: 1

VBA script to format addresses

I am trying to add data in Arcmap 10.4.1 to format addresses. I've never used any VBA or Python before. This code has worked to some degree. I am stuck with issues regarding double spacing and duplicate fields as in when a locality (town) and post town are the same. I was wondering if there is a way to only inset the fields if there are characters present and if there is a way to only insert POST_TOWN if it is different to LOCALITY_N

I tried following a tutorial for a VBA script to pull it all together in a new field. I added the code below which pulls all the fields together. 

[SAO_NUM] &" " & [SAO_TEXT] &" " & [PAO_NUM] &" " & [PAO_TEXT] &" " & [STREET_DES] &" " & [LOCALITY_N] &" " & [POST_TOWN] & " " & "LANCASHIRE" & " " & [POSTCODE]

An example of an entry is below where there are double spaces 

 UNIT 7   BROCKHOLES GARAGE WINDERMERE ROAD BROCKHOLES PRESTON LANCASHIRE PR6 0GH

Another example where the LOCALITY_N and POST_TOWN are the same is below

  20   ROSSALL GATE PRESTON PRESTON LANCASHIRE PR7 8AB

These are fictional addresses but with the same formatting issues. Any help or guidance appreciated

Upvotes: 0

Views: 227

Answers (1)

MBT86
MBT86

Reputation: 51

You can encapsulate your statement in a Replace(string,find,replacewith) vbscript function which as per the line below is used to replace double spaces " " with nothing "" string.

Replace([SAO_NUM] &" " & [SAO_TEXT] &" " & [PAO_NUM] &" " & [PAO_TEXT] &" " & [STREET_DES] &" " & [LOCALITY_N] &" " & [POST_TOWN] & " " & "LANCASHIRE" & " " & [POSTCODE],"  ","")

More details on this VBScript function can be found here: https://www.w3schools.com/asp/func_replace.asp

Upvotes: 1

Related Questions