I need formula help. I have a string field called "Market" that has some blank rows. What's the best formula or tool to use to replace empty or blank rows with text? So, if "Market" empty, then "NIC". I am not sure what I am missing. Thanks.
Solved! Go to Solution.
I would suggest using a formula tool with an expression:
IIF(IsEmpty([Market]), "NIC", [Market])
If you expect empty whitespace as well then use:
IIF(IsEmpty(TRIM([Market])), "NIC", [Market])
Because my friend James (@jdunkerley79) is a big fan of RegEx expressions, I couldn't resist adding an expression here:
REGEX_Replace([Market], '\0|^\s+$', "NIC")
The pattern for replacement above looks for a NULL or a string that is only space(s). If it finds either case, it will replace that with NIC otherwise it leaves it alone.
Cheers,
Mark
If there's the possibility that the blank rows are Null (as opposed to empty), you can add in a check for both:
if ( IsNull([Market]) or IsEmpty([Market]) ) then "NIC" else [Market] endif
This will insert "NIC" if the field is either blank or Null, and keep the text present in the [Market] field if it's not.
Thank you all! I appreciate the diverse ways of looking at it. No matter how many times I have done it I seem to always get tripped up on the Else Endif. Some how all my saved formulas were erased when I upgraded to 11. Is there anway for me to get them back?
I appreciate this one too so is the difference between using "IIF" vs "IF" is that I would not have to remember to use ELSE and ENDIF? I have used this in Tableau but I really did not know the difference. Thanks.
This formula came in very handy for me today, so thanks!!