[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 Hagar Delest on Wed May 29, 2019 8:08 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 21
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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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: 5284
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: 7221
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 18 times
User avatar
keme
Volunteer
 
Posts: 3208
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: 21
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: 21
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: 3208
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: 21
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 16 times
User avatar
keme
Volunteer
 
Posts: 3208
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: 21
Joined: Mon Apr 08, 2019 3:38 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests