# Conditional formatting formula character limits

#### johnboston

##### New Member
I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
250 characters plus sounds a lot of referencing, maybe it can be made smaller, if you post it

Sure, thanks. As background it's a tool that's being used for setting up scheduling on a 24 hour unit. The formula accounts for shift times (for example "730-1600" and length of shift, in this case, 8 hours). Here's what I'm trying to enter. It IS a cumbersome formula, I agree, but in shorter form it has worked. Open to any suggestions ...

=IF(OR(D4="730-1600",D4="1530-2400",D4=800,D4="0800 2330",D4="730-1600 r",D4="1530-2400 r",D4="0800 D"),8,(IF(OR(D4="730-2000",D4="730-2000 r",D4="1130-2400",D4="1130-2400 r",D4="0800 1930"),12,(IF(OR(D4=1930),4,(IF(OR(D4=2330,D4="D 2330",D4="d",D4="dr"),0,"?")))))))

There are spaces (for example in the first occurrence of "0800 2330" there are 10 spaces to account for appearance on the printed document) which, I think, count as characters(?).

Thanks, again for suggestions.

John

i guess this is a typo

="1530-2400",D4=800,D4="0800 2330",

numbers or text in the same cell

Yes, the 800 is supposed to be in quotes(?). I started this project months ago and it was recently revisited. It seems though it didn't matter whether the 800 was in quotes or not. I could certainly be mis-remembering on that.

I just checked it again with or without quotes before sending this and it didn't seem to make a difference in the result. It ought to be in quotes, though, is that one suggestion?

I guess, as additional background, what the spreadsheet is being used for is ... people request their shifts from a dropdown menu. For the working model I started out using, for example, the 5 most frequently requested shifts. When the sheet went active and began being used by more individuals the request was made, for example, for the next 5 most frequently requested shifts, as well. Meaning now, for example, I had to include 10 choices in the dropdown and update formulas.

Hope all this makes sense.

Thanks.

John

i'm not good at those style formulas, some very tuned in people here, know these

John,

There are ways we can possibly reduce that formula, or at least find a workaround. Can you please post some of your sample data, as well as describe exactly what your criteria is for the conditional format?

I'm not sure this is actually CF, just shift patterns to leave a marker, 4, 8, 12 and 0

I know this is a simple question but it's aggravating me and though it is probably previously addressed, I'm unable to find it. My question is this ...
In conditional formatting, is there a limit to the number of characters that may be entered in the third line from the right where the first line is "cell value is" and the second line is "not equal to"? I'm trying to type a lengthy formula (>250 characters including spaces and quotation marks, etc.) and at some point the line/box won't allow me to keep typing. I hope that makes sense. Thanks, John
Those little boxes are called refedits and yes, they are limited to 255 characters.

P.S.

It looks like you want to use the "Formula Is" option but you're still limited to 255 characters in the formula.

If you could provide a more detailed explanation of what you want to format we can probalby come up with something.

Last edited:

Replies
8
Views
309
Replies
1
Views
145
Replies
2
Views
698
Replies
2
Views
171
Replies
3
Views
187

1,216,174
Messages
6,129,296
Members
449,498
Latest member
Lee_ray

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back