I have a large address database point FC in FGDB format. Each address record is split into multiple fields like 'organisation', 'building name', 'building number', 'post town', 'post code' etc. Not every field is populated with data, there are many null values (not every address has an 'organisation' name for example).

I've created a new text field called 'Full_Addr' which will merge all the address fields into one. So far I used this VB script in field calculator:

Full_Addr= [OrganisationName]& ", " & [BuildingName]& ", " & [BuildingNumber]& ", " & [Thoroughfare]& ", " & [PostTown]& ", " & [Postcode]

However this results in entries like: , , 1, Example Street, Example Town, AB1 2CD or Example Organisation, , , Example Street, Example Town, AB1 2CD

Is there a smarter, more efficient way of creating a merged address field without the double, triple or quadruple ", "?

  • Can you please confirm whether you are using ArcGIS or QGIS as VB scripts are not used in the latter (and you originally tagged your question with qgis)? – Joseph Dec 12 '17 at 11:32
  • 1
    ArcGIS (also have QGIS if needed). Ah yes I typed in GIS as the tag and clicked the first suggestion... which was qgis it turns out! whoops. – Theo F Dec 12 '17 at 17:14

Use Python instead of VB Script. There are a lot more examples to learn from. For Example you can use IF statements to determine if a field is null, then continue concatenation or return the same input string. See this blog post: https://blogs.esri.com/esri/arcgis/2010/08/30/concatenate/

Specifically they provide a general purpose function for concatenation

# "*args" allows this routine to accept any number of field values.
# the values are passed as a Python tuple, essentially a
# non-editable list
def concat(*args):

  # Initialize the return value to an empty string,
  # then set the separator character
  retval = ""
  sep = "_"

  # For each value passed in...
  for t in args:
    # Convert to a string (this catches any numbers),
    # then remove leading and trailing blanks
    s = str(t).strip()

    # Add the field value to the return value, using the separator
    # defined above
    if s <> '':
      retval += sep + s

  # Strip of any leading separators before returning the value
  return retval.lstrip(sep)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.