[Solved] Conditional formatting digit count

[Hagar Delest]

Hi all,

I hope this is possible. If yes, hope someone could help me. I am trying to work on a conditional formatting, or formula if possible. But I prefer going to conditional formatting. For Example, the value of a cell should be 4-XXXXXXXXXXX (X = numeric, prefix "4-" remains the same or fixed for some reason) in short. 4-(eleven numerics). There are times when doing copy paste of that number on a specific Row, I miss 1 or 2 digits and only copied 4-(nine/ten digit numeric) which will result in data error/discrepancies which is inevitable and unnoticeable until you look closely or count the digit. Which is a pain in the ass to do every time I enter the data. I have to enter 50 to 80 cell data every day. So I want to check if there's a conditional formatting wherein if the cell has missing 1 or 2 digits entered (4-XXXXXXXXXX) the cell will turn to red or any background color. So I can easily notice if the data entered into the cell has missing digit. Another example, Correct data should be 4-12345678901 but if I accidentally pasted 4-1234567890 or 4-123456789, cell background should turn to red or any color.

[RusselB]

I think

Code: Select all   Expand viewCollapse view

and(left(A1;2)="4-";len(right(A1;len(A1)-2))<>11)

for a formula entry in the Conditional format will work for you, using A1 as a test address?

[FJCC]

You can set the first box of conditional formatting to Formula Is and the second box to

Code: Select all   Expand viewCollapse view

LEN(A2) <> 13

assuming your text is in A2.

[keme]

If you have regular expressions enabled in formulas, you can also use this conditional formatting:
"Formula is" ISERROR(SEARCH("^4\-[:digit:]{11}$";A1))

[jhonpaul72]

I think Mr. Keme got it. But another question though, would it be possible to apply this conditional formatting on the entire column B and not just cell B2?

[jhonpaul72]

Nevermind my last question. I think I got it. Just copy the cell B2 then Crtl + Shift + V and tick paste all to the entire column.

[keme]

With "paste special" (ctrl+shift+V) I believe that only "Format" needs to be ticked.

The "format paintbrush" will also work for conditional formatting.

[jhonpaul72]

Just as I thought. Just an additional question. I hope this will not mess up the entire thing. Sorry I just realized this when trying to apply this format. Aside from the 4-XXXXXXXXXXX that I am entering into the cell. I also Enter N/A text if the number is not applicable/available. In result, entering N/A on the Cell B is turning it to Red/Alert Formatted cell. I just want it to be just the Normal cell with white background when entering N/A on the cell. Is there a way to work around this?. I really do appreciate you guys trying to help me. To all who responded to my post.

[keme]

Make the search include "N/A" also. Regular expressions use the pipe character "|" for "or".

ISERROR(SEARCH("^(N/A)|(4\-[:digit:]{11})$";B2))

For a cleaner output, you can also add a condition for empty cell first, like it is done in the attached file.

[jhonpaul72]

Perfect. I think this covers all that I need. That's what I'm trying to figure out where to add the N/A on the formula and I'm just wondering what's the difference between "N/A" and (N/A). Because other of my formula I put in "N/A" for IF's function. And I also know that pipe character as a separator and always make sure "Enable regular expressions in formulas" ticked in order for this to work.