[Solved] Conditional formatting digit count

Discuss the spreadsheet application

[Solved] Conditional formatting digit count

Postby jhonpaul72 » Mon May 27, 2019 6:15 pm

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.
Last edited by jhonpaul72 on Mon Jan 06, 2020 6:06 pm, edited 3 times in total.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Open Office Conditional formatting digit count

Postby RusselB » Mon May 27, 2019 6:46 pm

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?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Open Office Conditional formatting digit count

Postby FJCC » Mon May 27, 2019 6:49 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Open Office Conditional formatting digit count

Postby keme » Tue May 28, 2019 6:41 am

If you have regular expressions enabled in formulas, you can also use this conditional formatting:
"Formula is" ISERROR(SEARCH("^4\-[:digit:]{11}$";A1))
Attachments
ErrorAlert.ods
(9.21 KiB) Downloaded 36 times
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional formatting digit count

Postby jhonpaul72 » Tue May 28, 2019 8:48 am

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?
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby jhonpaul72 » Tue May 28, 2019 9:34 am

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby keme » Tue May 28, 2019 10:08 am

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

The "format paintbrush" will also work for conditional formatting.
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional formatting digit count

Postby jhonpaul72 » Tue May 28, 2019 10:53 am

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby keme » Tue May 28, 2019 2:59 pm

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.
Attachments
ErrorAlert.ods
(7.91 KiB) Downloaded 37 times
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional formatting digit count

Postby jhonpaul72 » Tue May 28, 2019 3:33 pm

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby jhonpaul72 » Mon Jan 06, 2020 10:35 am

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby RusselB » Mon Jan 06, 2020 3:42 pm

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Conditional formatting digit count

Postby jhonpaul72 » Mon Jan 06, 2020 5:16 pm

Actually there's no predictable pattern. The only fixed value is the prefix "4-"
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: Conditional formatting digit count

Postby RusselB » Mon Jan 06, 2020 5:52 pm

Try replacing :digit: in the regex with a-zA-Z0-9
That should allow upper & lower case letters and digits
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Conditional formatting digit count

Postby jhonpaul72 » Mon Jan 06, 2020 6:04 pm

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.
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm


Return to Calc

Who is online

Users browsing this forum: fiona87 and 21 guests