Formula

[Arkouda]

Hi All 

I have this formula currently and need to have it Identify blanks vendor cells (our data comes in with Vendors identified as ALL Caps and other - employee spend as lower case) but we also have blanks and what them identified as Vendor. 

Can someone assist me with this? I tried the isempty formula but keep getting errors

IF Regex_Match([VENDOR_NAME], Uppercase([VENDOR_NAME]), 0)
THEN 'Vendor'
ELSE 'Other'
endif

[Hannah_Lissaman]

Hi!

I would suggest breaking this down into steps.

First, create a formula just to identify the blanks. IsEmpty is the correct formula to use. If this fails to identify some of the blanks, you can now find them and try to find out why. 

The most likely reason is that there are spaces in fields which look blank. Try using IsEmpty(Trim([Field])) to remove leading and trailing spaces. 

Once this is working, we can combine this with your formula:

IF IsEmpty(Trim([VENDOR_NAME]))

THEN "Vendor"

ELSEIF Regex_Match([VENDOR_NAME], Uppercase([VENDOR_NAME]), 0)

THEN "Vendor"

ELSE "Other"
ENDIF

[Jonathan-Sherman · Accepted solution]

Hi @Arkouda,

Depending on whether or not your blank Vendor Name cells are empty or null you'll want to use:

Blank vendor name cells are null

IF Regex_Match([VENDOR_NAME], Uppercase([VENDOR_NAME]), 0)
THEN 'Vendor'

ELSEIF IsNull([VENDOR_Name]) THEN 'Vendor'
ELSE 'Other'

ENDIF

Blank vendor name cells are empty

IF Regex_Match([VENDOR_NAME], Uppercase([VENDOR_NAME]), 0)
THEN 'Vendor'

ELSEIF IsEmpty([VENDOR_Name]) THEN 'Vendor'
ELSE 'Other'

ENDIF

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan