Character Limit Formula

rwilkes

I'm unable to convert this to column formula (probably due to the reference to my 'blank' column… However, this formula doesn't work,

=IF(LEN([Source Admission ID]) >= 21, "Source Admission ID Limited to 20 VARCHAR", "")

So, I changed it to this, which works…

=IF(LEN([Source Admission ID]) >= 21, "Source Admission ID Limited to 20 VARCHAR", Blank5).

The reason that the below formula doesn't work is that I have a condition set to check that field and if it's 'empty' to highlite it… "" actuall fills the field so it's not 'empty'. :( Hence, why I created a "blank" column to reference to…

=IF(LEN([Source Admission ID]) >= 21, "Source Admission ID Limited to 20 VARCHAR", "")

rwilkes

…I figured it out… @row was the trick, but at the end of my formula - it still referencees the 'blank' in the Blank column (thank you):

=IF(LEN([Source Admission ID]@row ) >= 21, "Source Admission ID Limited to 20 VARCHAR", Blank@row )

Hello, it's more complicated than that… My formula works, but I have conditional formatting looking at that cell and if it's blank it's supposed to highlight it… "" is not 'blank', and therefore conditional formatting isn't effective. Is there a way to make it blank/null so whereas conditional formatting sees that cell as blank? Again "" is recognized as <> blank in conditional formatting.

The below formula works perfectly, but I can't convert it to column formula, I can just drag it down…

=IF(LEN([Source Admission ID]@row ) >= 21, "Source Admission ID Limited to 20 VARCHAR", Blank1)

By having it reference to the Blank Column (Blank1), which is blank then conditional formatting triggers off that 'blank' that is actually carried into the formula. (Blank1 in the blank column is actually a blank), whereas "" isn't considered a blank in conditional formatting. @Chlee

Here's the conditional formatting it's referencing:

Victoria_Indimar

Hi @rwilkes ! Your first formula should work… Can you add an @row, then try to convert to Column Formula? Does that work?

=IF(LEN([Source Admission ID]@row) >= 21, "Source Admission ID Limited to 20 VARCHAR", "")

rwilkes

…I figured it out… @row was the trick, but at the end of my formula - it still referencees the 'blank' in the Blank column (thank you):

=IF(LEN([Source Admission ID]@row ) >= 21, "Source Admission ID Limited to 20 VARCHAR", Blank@row )

Hello, it's more complicated than that… My formula works, but I have conditional formatting looking at that cell and if it's blank it's supposed to highlight it… "" is not 'blank', and therefore conditional formatting isn't effective. Is there a way to make it blank/null so whereas conditional formatting sees that cell as blank? Again "" is recognized as <> blank in conditional formatting.

The below formula works perfectly, but I can't convert it to column formula, I can just drag it down…

=IF(LEN([Source Admission ID]@row ) >= 21, "Source Admission ID Limited to 20 VARCHAR", Blank1)

By having it reference to the Blank Column (Blank1), which is blank then conditional formatting triggers off that 'blank' that is actually carried into the formula. (Blank1 in the blank column is actually a blank), whereas "" isn't considered a blank in conditional formatting. @Chlee

Here's the conditional formatting it's referencing: