locked
IF(ISBLANK) not working RRS feed

  • Question

  • Tried several expressions to simply return "Last, First" if MI is null. If not then "Last, First MI". None seem to work.

    =IF(ISBLANK([MI]),CONCATENATE([Last],", ",[First]),CONCATENATE([Last],", ",[First]," ",[MI]))

    also,

    =IF(ISBLANK(MI),Last&", "&First,Last&", "&First&" "&MI)

    Thanks


    John Fletcher

    Saturday, May 19, 2018 5:20 PM

All replies

  • HI,

    Try this. It works.

    =IF(LEN([MI]) > 0,CONCATENATE([Last],", ", [First], [MI]),CONCATENATE([Last],", ",[First]))

      


    Ramakrishnan


    Please remember to mark the replies as answers if they helped.

    • Proposed as answer by SB Ali Tuesday, May 22, 2018 10:53 AM
    Sunday, May 20, 2018 1:01 AM
  • Thank you for the response but it still does not work. I get a #NULL! in the field if there is no MI.

    John Fletcher

    Sunday, May 20, 2018 2:06 PM
  • Hi,

    What’s your field type and settings?

    As I tested in my local, if MI is text filed, the calculated value is calculated correctly with your formula.

    =IF(ISBLANK([MI]),CONCATENATE([Last],", ",[First]),CONCATENATE([Last],", ",[First]," ",[MI]))


    Best Regards,

    Lee


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, May 21, 2018 6:24 AM
  • Thanks Lee but still not working. Get "#NULL!" in the field if [MI] is null.

    John Fletcher

    Monday, May 21, 2018 3:04 PM
  • Hi,

    Could you share your field type and field settings so we could try to research(it would be better with sample data)?  

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, May 22, 2018 12:49 AM
  • Here are screen shots of the field types and List view.


    John Fletcher

    Tuesday, May 22, 2018 2:58 PM
  • Hi,

    Based on your screenshot, seems you’re using SharePoint online.

    While I can’t reproduce in my SharePoint online environment also.



    Could you test by creating a simple test list?

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, May 23, 2018 8:18 AM
  • It works when creating from "Add List" but I created a Custom list from "Add App" so not sure why one works and the other doesn't. Unfortunately, I have 18 Lists that were created from the "Add App" dropdown which means no expressions will work in any of my Lists.

    John Fletcher

    Wednesday, May 23, 2018 11:06 PM