How to limit number of characters in the cell

Hello guys,

I want to ask you for a small advice.

What should I do If I want to limit the number of characters/numbers i
the cell.

For instance: I have column named "phone numbers" and I want to inser
only numbers which consist of 6 numerals (it means that max cell valu

Many thanks and best regard

Toman's Profile:
View this thread:

2/10/2006 9:52:30 AM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 28

Hello Toman,

Have a look at Data / Data Validation

You can define a) valid text length b) valid interval etc.


bplumhoff1 (208)
2/10/2006 11:41:41 AM
Hi Toman,

Easiest way is Data>validation> whole number > between and then enter
the min and max eg 100000 and 999999.


EdMac's Profile:
View this thread:

2/10/2006 11:45:17 AM

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin "Lindsay" <> wrote in message > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? Hi, =left(a1,3) "SaraMack" wrote: > I need to look up "c...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: View this thread:

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

limit the number of outgoing messages
Hi, who can I limit the number of concurrent outgoing messages? for example: if I have a queue of 1000 messages, how can I force exchange to send only 50 messages at a time? ...

Limit on Exchange 2003 version included with SBS 2003
Can anyone tell me if the version of Exchange 2003 included with Small Business Server 2003 has the 16GB limit that exists in the Exchange 2003 standard edition? Yes. >-----Original Message----- >Can anyone tell me if the version of Exchange 2003 >included with Small Business Server 2003 has the 16GB >limit that exists in the Exchange 2003 standard edition? >. > ...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: --- Regards, Norman "swiftcode" <> wrote in message > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Numbering in Book Files
I am creating a tech manual where each chapter is its own file. I have created an outline numbered list and saved the list in a template that I then use to create the new chapters. However, I am finding that when I go into a new chapter that the outline numbering does not pick up on the chapter (Heading 1) number. For example: Chapter 1: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. But when I start my next chapter, when I update the chapter number to start at 2, I get this: Chapter 2: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. ...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

How do I create upper/lower case letters in cells?
I have a large spreadsheet with names/addresses that are all capitalized. I want to make them upper and lower case (SMITH = Smith). What's the formula? You could create helper cells with this formula =proper(A1) "boz130" wrote: > I have a large spreadsheet with names/addresses that are all capitalized. I > want to make them upper and lower case (SMITH = Smith). What's the formula? ...

Report: Cell #1, Cell #2, Cell #3, Cell #4
I am stuck again and would love som help :( I would like to repeat all words found inside ~25 cells, separated only by ", ", ignoring empty cells. Data: A1: [Apple ] A2: [Orange] A3: [Banana] A4: [Tomato] A5: [Syrup ] A6: [ ] A7: [ ] A8: [ ] The result should be something like: [Apple, Orange, Banana, Tomato, Syrup] -- JemyM ------------------------------------------------------------------------ JemyM's Profile: View this thread:

Errror Help: character ' ', hexadecimal value 0x2 is illegal in xml documents
what does this error mean? how can i fix this? thanks, raj * raj wrote in microsoft.public.dotnet.xml: >what does this error mean? how can i fix this? You have an octet 0x02 or perhaps a character reference &#x02; in the document. This is not allowed in XML 1.0; the latter is allowed in XML 1.1 but your version of the .NET framework probably does not support that, and most likely you have not declared this version either. In other words, your document includes random binary data and is there- fore not an XML document. You have to correct the document before you can successfully...

Locating a number for a MS office
I have lost the boklet that accompanies my CD. However, I am unable to locate a number to contact someone. Any thoughts. I think if you ever learned how to contact them directly, they'd put a hit out on you. (Seriously, visit the MS website and drill down through the tech support area until you find the numbers you need. Good luck.) -- The problem with resting on your laurels is that eventually you are sitting on dead branches. JoAnn "ziggy" <> wrote in message news:0a4c01c36747$0fb00a40$a001280a@phx.gbl... > I have lost the boklet that...

Two calendar dates in one cell..??
I have a Microsoft Publisher calendar document that was created in 2005 that shows the dates Monday - Sunday. When I print it, it will not print out any dates that fall after the fifth week (eg January 2006 it prints up to January 29th but not the 30th or 31st). These are in an "overflow" area. But when I open the calendar part in Word to edit the calendar, it appears normally and that sixth week shows up just fine. I need that sixth week to print OR I need to do the split date feature (I would prefer the split date). How can I set up the numbers in one of those cells so i...