Filling in a Column with Coalesce not Working

Do you know why this coalesce function is not working? I’m trying to fill the null values with my Stock Id.

Hey Zachary!

This looks like it is not working because the values in Order # are in fact not null. They look as if they are empty strings – which if that is the case coalesce would not work. If you use the formula below I think that will work for you!

If([Order #] = " ", [Stock Id], [Order #])

Also, if you are not sure if a field is a null, an empty string or a space, you can write this more complex formula:

If(Trim(Coalesce([Order #],"")) = “”, [Stock Id], [Order #])

Here is how this works:

  1. If [Order #] is Null, Coalesce() will convert it into a “” (blank string)
  2. If [Order #] is a space or multiple spaces, Trim() will convert it into a “” (blank string)
  3. Now the If() takes the blank string and converts it into [Stock Id]