1

I am moving an Access database to Sharepoint 365 lists and constructing a few PowerApps. I have fallen at the first fence......

The Access database held postal address information across 6 fields,

Address_Line_1, Address_Line_2, Address_Line_3, Town, County, PostCode

The relevant table was directly exported to the required sharepoint list, therefore the addressing fields generated 6 Sharepoint list columns. I wish to hold the Addresses in ONE Sharepoint list column, so I thought this would be easiest resolved using a calculated column and so (in a similar method I would use in Excel) I set the function of a new calculated column to be,

=[Address_Line_1]&", "&[Address_Line_2]&", "&[Address_Line_3]&", "&[Town]&", "&[County]&", "&(PostCode]

However when I looked at this new column some entries gave the full address whereas others resulted in "null"......by trial and error it appears that if any of the columns is empty this "null" will be result. The vagaries of the UK postal address system will result in no Address_Line_2 and/or Address_Line_3 entries 60-70% of the time, hence this will not work for me

Is there something I am missing to make this calculated column work as I expected?

1

Test for the Nulls (blanks), and supply a substitute value.

Replace [Address_Line_2]&", " with ISBLANK([Address_Line_2], "", [Address_Line_2] & ", " )

=[Address_Line_1] & ", " 
 & ISBLANK([Address_Line_2], "", [Address_Line_2] & ", " )
 & ISBLANK([Address_Line_3], "", [Address_Line_3] & ", " )
 & [Town] & ", " & [County] & ", " & [PostCode]

The above assumes you don't want empty ", ," sections.

  • Mike, many thanks, and your lines explain the ISBLANK statement – GeoffE Jun 9 '18 at 20:26

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.