Conditional Formating Furmula

I have to enter a 14 digit licence number in a field this is a mix of numbers 
and leters. can some one please give me a formula that i can use in 
conditional formating tol show if there are to few or to many digits. (Using 
excel 2003) Thaks in advance.
0
Utf
12/4/2009 11:42:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
511 Views

Similar Articles

[PageSpeed] 22

CF/ Formula is/ =LEN(A1)<14
Format as desired for too few

CF/ Formula is/ =LEN(A1)>14
Format as desired for too many

Rather than using CF you could, of course, use Data validation to require 
LEN(A1) to be 14.
--
David Biddulph

"jockj215" <jockj215@discussions.microsoft.com> wrote in message 
news:A2412A36-19CD-403A-A0EB-694C100547B1@microsoft.com...
>I have to enter a 14 digit licence number in a field this is a mix of 
>numbers
> and leters. can some one please give me a formula that i can use in
> conditional formating tol show if there are to few or to many digits. 
> (Using
> excel 2003) Thaks in advance. 


0
David
12/4/2009 12:01:16 PM
You can deal with this with conditional formatting or data validation, or both.

Formula for conditional formatting (for cell A1)
=LEN(A1)<>14
and set your format for the not-14-characters condition.

You can also use Data Validation and use 'Text Length', 'exactly' and enter 
14 as the length.


"jockj215" wrote:

> I have to enter a 14 digit licence number in a field this is a mix of numbers 
> and leters. can some one please give me a formula that i can use in 
> conditional formating tol show if there are to few or to many digits. (Using 
> excel 2003) Thaks in advance.
0
Utf
12/4/2009 12:03:01 PM
Spot on and silly me forgot about data valadation

Thanks


"JLatham" wrote:

> You can deal with this with conditional formatting or data validation, or both.
> 
> Formula for conditional formatting (for cell A1)
> =LEN(A1)<>14
> and set your format for the not-14-characters condition.
> 
> You can also use Data Validation and use 'Text Length', 'exactly' and enter 
> 14 as the length.
> 
> 
> "jockj215" wrote:
> 
> > I have to enter a 14 digit licence number in a field this is a mix of numbers 
> > and leters. can some one please give me a formula that i can use in 
> > conditional formating tol show if there are to few or to many digits. (Using 
> > excel 2003) Thaks in advance.
0
Utf
12/4/2009 12:36:01 PM
Reply:

Similar Artilces:

html table import
Hi Our sales organization came up with the idea of taking a web based financial tool, write a huge Excel sheet around it, and update the data every week. From it, they get all kinds of stats and results for the management. So far so good. I'm in charge of the web application. Now since a few months, when they import, after exactly 139 lines, the formatting changes in excel. Instead of being right aligned, the table cells are suddenly left aligned (starting on column i), the 0.00 in the source is suddenly shown as such where it was 0 in the 138 lines before. Then after a few hundred...

Macro that deletes values with condition
Hi, I need a macro that deletes duplicates of numbers that appear an odd number of times and that deletes duplicates and the value duplicated an even number of times. Example: Original data A 1 2 3 1 2 1 2 3 4 Result: A 1 2 4 Values 1 and 2 must remain and only delete duplicates because they appear an odd number of times (3), 3 must be deleted because it appears an even number of times (2) and 4 appears because it has no duplicates. Hope this can be done! Thank you so much Hi Insert a heading in row 1 and try this macro: Sub aaa() Dim f As ...

CONDITION IN MACRO #2
This is an update to an earlier question I posed. I am using Access 2002. I'm trying to use a Condition in a Macro to determine whether or not to SetValue. It appears that the Condition will not allow me to use a "wildcard" (asterisk). The Field I'm checking is filled with entries like CRUZ SA or CRUZ SB or CRUZ SC. I simply want to set the Condition to look for CRUZ*...meaning any entry that begins with CRUZ. The Condition I set up is [CLASS]="CRUZ*". It doesn't work. But, when I set the Condition to [CLASS]="CRUZ SA" it works. Access Help seem...

formating (000)
Is there any way I can format numbers so only thousands show. Instead of: 100,000 I'd like to show 100 1,000,000 I'd like to show 1,000 (I can just specify in the column heading that all numbers are (000).) Is this done using custom formats? Going through and dividing by 100 seems like a pain. Thank you Hi try the custom format 0,.0 -- Regards Frank Kabel Frankfurt, Germany John wrote: > Is there any way I can format numbers so only thousands > show. > Instead of: > 100,000 I'd like to show 100 > 1,000,000 I'd like to show 1,000 > > (I can ...

Clear Formatting Icon
I have a client who has a template with Arial 20 pt regular as the default font, with all the master text in Arial. When she clicks the clear formatting icon, the text changes to Helvetica, Grey, 36pt, bold. She checked the master slides to see if it was picking up Helvetica from any of the place holders there, but that's not the casse. Does anyone know where the Helvetica might be coming from? She's using PowerPoint 2007. Thanks! Might be coming from the default shape or textbox. If you create a new manual textbox, what font is used? -- Echo [MS PPT MVP] http:...

more than 3 conditional formats #2
Is it possible to add more than 3 conditional formats to a particula cell? Any help would be much appreciated. Thanks, - -- Message posted from http://www.ExcelForum.com Hi A No, you'd need a macro to do the formatting with >3 conditions. HTH. Best wishes Harald "abailey >" <<abailey.18da03@excelforum-nospam.com> skrev i melding news:abailey.18da03@excelforum-nospam.com... > Is it possible to add more than 3 conditional formats to a particular > cell? > > Any help would be much appreciated. > > Thanks, > -A > > > --- > Me...

Formatting cells with Time data
I'm trying to set up my spreadsheet to format my cells to hhmm, but every time I try to enter data into the cells, the system automatically converts the hours I've entered into a date and leaves the time value at 0 any suggestions as to what I'm doing wrong? Are you entering as hh:mm regardless of the cell format? -- __________________________________ HTH Bob "yimkhoc" <yimkhoc@discussions.microsoft.com> wrote in message news:A481A01F-0938-4F38-A93C-F2B9CB0BD34C@microsoft.com... > I'm trying to set up my spreadsheet to format my cells to hhmm, but ev...

Count ifs
Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office...

HexaDecimal format with Cstring
I would like to generate a striung in HexaDecimal format from a numeric value. I use CString format for this dValue = 20.0; CString cstrValue; cstrValue.Format(_T("%#0X"), dValue) But 'X' will cause the prefix as well as the Hexadecimal characters as block letters and if i use 'x' it wiull use small letters everywhere. Is there any possibility to mix this up so that I can get an output for example '0x2AB' i.e. x must be small and the rest be in capital. 0x%0X why worry about how to fool the system into solving a non-problem? Note that if the value yo...

Formatting all data series in a chart
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel If I, for example, want to add value labels to all series in a chart (or change line width or anything else that normally would be similar for all series in a chart), I have to select each one and make the change for each of them. <br><br>Is there a way to target all data series at once? (I would expect shift-click or cmd-click to select multiple series, but that doesn't work.) ...

Conditional Format
Hi, I am creating a conditional format which is based on a value form another cell say: if value is less than: =If(r14=14,20,30) however can I set the cell to be relative rather than one cell? Thanks You should be able to copy the cell with the conditional formatting, then Paste>Special>Formats over all the cells you wouold like to have the conditional formatting. "MS Forum Newsgroup User" wrote: > Hi, > > I am creating a conditional format which is based on a value form another > cell say: > > if value is less...

Please help with Conditional Formatting
Hi all, I am trying to get a cell to change color when: D5 contains "/" in the form of for example: CM/F/CM or C/C I tried different variations of: Formula Is------------------$D$5= "*/*" but it does not work, any ideas? Thanks for nay help, Emilio Hi! Try this: Formula Is: =ISNUMBER(SEARCH("/",A1)) Biff >-----Original Message----- >Hi all, > >I am trying to get a cell to change color when: > D5 contains "/" in the form of for example: > >CM/F/CM or C/C > >I tried different variations of: > >Formula Is...

ComboBox formatting
Hi All, In a query I formated a field to allow the sorting to come out in sequence. x = 4 Number: format(x,"000000") I have combo box based on this query and I would like to be able to type in 4 without the leading zero's. However it errors when doing so. I have tried to use: cbConAudit_id = Format(cbConAudit_id, "000000") on after update, on dirty, on change, but nothing is working. I've also used the format in the properties using 000000, but still errors. Is there a way to keep from adding the leading zeros? why dont you change for field ...

Dollar and date format
I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick For Question # 1 - Multiply by 100 :-)) Just kidding For Question # ...

Importing the format (Excel 2003)
I'm using a vlookup function to import data from a pivot table but I'd like to import the format as well. From previous posts this is apparently not possible unless you use codes. I'm unfortunately not familiar with coding. Is there maybe a site you could direct me to where I would be able to figure out what I need to do? Any links to sites where one could learn a bit more about macros and coding would be helpful as well. Hi Jaco, After entering the v-lookup formulae in the required cell, you can use paset - special format from the respective pivot field. -- Dilip Kumar Pandey...

Format TextBox to Currency in a Form
Hi, I am having trouble formatting textbox in a form. Here is a little background on what i did in a form. I have a combobox that has all the company names. I am populating textbox results by selecting company name from the combobox. For Example, If i select CompanyA from the combobox then the textbox1 will populate its revenue. But, its populating in a general number format without any decimals. I tried everything from changing the property in the textbox and the source table. But i have no luck getting the Currency in the format. Can someone please help me with my isssue. ...

Sending Chart as Snapshot Format
How I can send pivot chart as Snapshot Format? Or how I can send pivot chart by email ( I use send object in macro) but when I select form then there was not output format as Snapshot what should I do? -- Message posted via http://www.accessmonster.com You might also try printing to a PDF. There are several free PDF creators including CutePDF: http://www.cutepdf.com and one excellent inexpensive program, Win2PDF: http://www.win2pdf.com -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Reza via AccessMonster.com" <u2845...

Format page number in Words Document
Hi, Appreciate if you can help me here. I am trying to number my pages in my document a bit differently using the header and footer but I can't seem to get around doing it. Normally the page numbers will run concurrently in the header or footer as in page 1,2,3,4...etc But what intend to do is add page 5 (a) after page 5 and before page 6. However when I do that under the header or footer page 5 becomes page 5 (a) How do I format this under the header or footer? Thanks You will need to use the following field construction { IF { PAGE } > 5 { IF { PAGE } &...

E-mail message formatting.
Running Pub 2003 on XP Home. I edit a newsletter for a guild every month and use the contents of several member E-mails in the publication. (Presidents message, etc.) When I copy and paste the text from some--not all--of the E-mails into a text box they show up with each line in a separate "cell". I can clear that up by pasting the message in Word 2003 first, but I wondered if there is a quicker method. I'm a bit confused about the right click "paste special" options and how HTML figures in. Appreciate the help! Cynthia C The quickest method is to have Note...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Compiling MC file in Unicode format
Hi, I have an urgent problem here with regards to MC files. I have created a .mc file which contain messages in English, Japanese, Chinese, etc. According to the example given on Microsoft website (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tools/tools/sample_message_text_file.asp), the file is stored in Unicode (UTF-8) format, so if I view the file using a Unicode-compatible editor, I can actually see the messages in its respective languages. Now, according to Microsoft documentation, I should be able to now compile the .mc file and then generate different lan...

format a percentage figure
Hi to everyone i have a worksheet where i am retrievine data from access so i bring this data to another worksheet be equation. the number is already as a percentage in the database but it appears like 9,56 (it really means 9,56%) in the worksheet i want to have that number but if i am trying to format it as a percentage it change it to 956 % There is anyway to bring it witha equation but manually to format it and appears like 9,56 % regards bill ...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

Outlook 2007 PST Format for IMAP
In outlook 2003 you could not use the new Unicode format for .PST files - and were stuck with the (97-2002) format file. Has this changed in outlook 2007? Thanks! EA EastAsia <Private@AsiaWorld.com> wrote: > In outlook 2003 you could not use the new Unicode format for .PST > files - and were stuck with the (97-2002) format file. Has this > changed in outlook 2007? I believe I read that it has. -- Brian Tillman ...

Excel default formatting of numbers
I have a CSV file which is generated by an inhouse application. One of the columns in this file has a four digit code, which are text but made of numerical digits. Some of the codes start with one or more zeroes, ie 0012, 0013, 0014 etc. The problem is when my users open the file in Excel the column has been formatted as numbers and the leading zeroes have been trimmed, so 0013 becomes 13. I do not want my users to have to play around with formatting. Also even if I do select the column and change the formatting to text the leading zeroes are still missing. Please could anyone help me with a ...