[Solved] Conditional formatting digit count

[jhonpaul72]

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

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

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.

[jhonpaul72]

Hi. It's been a long time. I have another question to this conditional formatting that was resolved. This time, I am trying to enter a data that is "4-(7 digit combination of letters and numbers)" for example, 4-9G6NQ9W. I tried to use the formula on the resolution but looks like it's not working. Maybe because it's only applicable to all numeric digits. Is there anyone who can help me with combination of numbers and letter?. Thanks for the help in advanced.

[RusselB]

Is there a specific numeric to alphanumeric order? Eg: 2 numbers, 3 letters, 2 numbers?
or, as shown in your example, number, letter, number, letter, letter, number, letter?
or is there no predictable pattern?
Predictable patterns are easier to handle.

[jhonpaul72]

Actually there's no predictable pattern. The only fixed value is the prefix "4-"

[RusselB]

Try replacing :digit: in the regex with a-zA-Z0-9
That should allow upper & lower case letters and digits

[jhonpaul72]

Awesome. Thanks for your help. Looks like that one worked like a charm. I was thinking that the :digit: might be the problem and needs to be changed and I was right. The problem though is that I don't know what to replace or how to correct it So you did it. Thank you.