Enforce format

How can I "enforce" a format in a cell in excel so that it allows number 
entry only, i.e. it does not allow letters or any other characters.
Thanks in advance.
Keith
0
Elfey1001 (1)
10/12/2009 8:19:01 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1923 Views

Similar Articles

[PageSpeed] 31

Data Validation; custom formula; If active cell is A1, use this:
=NOT(ISERROR(1*A1))

"Elfey1001" <Elfey1001@discussions.microsoft.com> wrote in message 
news:D744EEFC-3BD0-43A7-B673-F16107BBFA03@microsoft.com...
> How can I "enforce" a format in a cell in excel so that it allows number
> entry only, i.e. it does not allow letters or any other characters.
> Thanks in advance.
> Keith 


0
rumlas (268)
10/12/2009 8:28:34 PM
In EXCEL 2007:-

Data / Data Tools / Data Validation / Data Validation / Settings tab / 
Allow: field - in here you can do what you want plus a variety of other 
options. 

If my comments have helped please hit Yes. 

Thanks! 




"Elfey1001" wrote:

> How can I "enforce" a format in a cell in excel so that it allows number 
> entry only, i.e. it does not allow letters or any other characters.
> Thanks in advance.
> Keith
0
info9487 (39)
10/15/2009 4:55:02 PM
Reply:

Similar Artilces:

Name, Date and Address Formats
Hi, I have a number of different spreadsheets that I need to impirt into a single database. Unfortunately some of the information is in different formats. Would anyone be able to provide me with a solution that can turn: A B Brown ABC Brown *A B C*Brown* Brown, A B Brown, Anthony Bernard Brown Anthony Bernard Mr A B Bernard Into separate fields for title, forename, initials & surname On a similar theme I also need to do much the same thing with addresses: 1 Victoria Road, Victoria, Victoriashire, VC1 1VC etc And lastly (for now anyway) I also have some dates that I need to ...

Short / long date format bug?
The computer is set to use the long date format and everything is OK until I make changes to a chart which causes all dates in all open worksheets to be displayed in the short date format. When excel is closed down and restarted the problem is resolved - but doing this is annoying. I can't see if this a known fault or if it has been introduced through a patch. Does anyone know anything about this strange behaviour please? Thanks. Bill Ridgeway Since posting this I haven't had one response. I can't believe I'm the only user experiencing this problem. Every time I...

Check Format with Check on top check/stub/stub
I'm trying to print checks with the check on top and two stubs on bottom. I've followed KB864284, but my check still come out stub/check/stub. I've given myself access to the modified report in security and changed the check format in payables. Nothing works. HELP! I'm on GP 9.0 mf Have you given yourself access to the actual modified check? The modified check neds to have a * by it (security, user - correct compnay, Modified reports, Purchasing) "Mark" <Mark@discussions.microsoft.com> wrote in message news:E11A5373-6EAC-48DF-8678-281879FF57BC@mi...

Specialized Cell Formatting
I have a column that contains a string of numbers, but I need to have it formatted in a customized way using a few .'s. And I want to know if there is any way I can set the formatting for this column so that it will automatically change all the cells in this column without me having ot manually do it. Here's an example of what I need: In each column a string of numbers like: 123456789012345 The string of numbers need to be formatted like so: 123456.78.901234.5 The column is already filled with numbers in the first example. Can I somehow automate the formatting of this string of nu...

Date Format turn to Year #2
From: learning_codes@hotmail.com - view profile Date: Thurs, Nov 16 2006 9:24 pm Email: "learning_co...@hotmail.com" <learning_co...@hotmail.com> Groups: microsoft.public.excel Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse | Find messages by this author Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =3DYear(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated...

formatting: highlighting, wrap text, etc.
Hi, I have used Excel for years and in the past couple of days I have not been able to highlight any fields, cannot format an entire document, i.e., Ariel 10 pt I have tried on-line help and can't figure out how to resolve. I have even opened new workbooks and continue to have the same problem HELP!!!!!!!!!! thanks. A few ideas. Check all Excel Tools/Options. Check Windows Display options. Faulty keyboard. Faulty mouse. Scroll lock on. Faulty monitor/screen display settings. Installed/amended any software recently ? Start/Run... Type excel.exe /regserver .... Enter --- Message post...

Conditional format to determine if last character in a cell is a number or a letter
I have a spreadsheet where I enter weights. The weights can be any whole number 1-99999. I would like to be able to flag suspicious weights using conditional formatting by putting a letter as the last character, such as 123a, or 57b, 4471c, etc.What conditional formatting formula can I use to accomplish this? Thanks, Tonso Assuming your weights are in column A, starting with A2, highlight the cells in that column with A2 as the active cell, and use this formula in the CF dialogue box: =3DAND(CODE(UPPER(RIGHT(A2)))>=3D65,CODE(UPPER(RIGHT(A2)))<=3D90) Then click on the Format button a...

Customers & Suppliers Trial Balance in Debits & Credits Format
Hi, Do anyone have a method to bring the Customers & Suppliers Trial balance in debits and Credits format just like how the G/L Trial balance works. Most of the clients are repeatedly asking for this requirement. The report should have Customer ID, Docnumber, Doc Date, payments, receipts, returns, credits accordingly falling into the debit/credit buckets with running balance totals. If the report can be picked up with any date range also, it'd be most beneficial. Thanks Janakiram M.P. ...

Format conversion #2
I have a column of numbers defined "date" by a "dd/mm/yyyy" format, now for my commodity I enter dates by hitting "dd-mm-yyyy" where often dd and mm are made of only one figure, now it often happens that these dates are converted in the wanted dd/mm/yyyy format, but often they don't where am I wrong? il barbi On Mon, 18 Oct 2010 11:35:17 +0200, "il barbi" <angeieri.barboggi@ngi.it> wrote: >I have a column of numbers defined "date" by a "dd/mm/yyyy" format, now for >my commodity I enter dates by hitting "...

Conditional Formatting #26
Can I do conditional formatting with more than 3 conditions. Thanks in advance. Rgds, Itty. --- Message posted from http://www.ExcelForum.com/ Hallo, it's not possible to have more than 3 conditional formatting. I looked even in visual basic but the helpfile said that when you trie more then 3 it gives an error. so no more then 3 Greetings --- Message posted from http://www.ExcelForum.com/ Hi Itty, actually you can apply 4 different formats (3 through the conditional format dialog and one as default format). For more than 4 different formats you need VBA code (process the workbo...

Excel currency format does not translate correctly in a Word merge
When I merge an Excel file with various fields/formats, the currency formats [seven different columns with currency] the Word file displays 70 instead of $70.00. What do I need to do and in which file? "ILoveMyCorgi" <ILoveMyCorgi@discussions.microsoft.com> wrote in message news:FB51D073-B13F-47C5-B77C-8C7007C62C3F@microsoft.com... > When I merge an Excel file with various fields/formats, the currency > formats > [seven different columns with currency] the Word file displays 70 instead > of > $70.00. What do I need to do and in which file? Exc...

Pasting high volume looses formatting?
With Excel 2002, when attempt to copy a large spreadsheet, I get the following (generic error code) when trying to paste "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway? Of course when I answer "OK" I loose the formatting (primarily row height). I am trying to keep two worksheets within one workbook in sync...updating one then copying to the second and sorting/manipulating the second. Sheet is 25 cols by 250 rows with varying fonts, row heights, column sizes and colors This happens when I try to paste into ...

Time input mask format
If I input - "340 a", I want it to show "3:40 AM" or if I input 1230 p, I want it formated as 12:30 PM can this be done? enter your times as 3:40 or 12:30 and set the format of the cell to show it as am or pm format ,,,, cells,,,,custom,,, h:mm AM/PM "WNB-96740" wrote: > If I input - "340 a", I want it to show "3:40 AM" > > or if I input 1230 p, I want it formated as 12:30 PM > > can this be done? There is no way to set up an input mask as you describe without using VBA. See Chip Pearson's site for quicken...

Allow data changes but prevent formatting changes
Is it possible in Excel 97 to protect a spreadsheet in such a way tha data can be entered in unlocked cells but formatting changes cannot b made? I have a spreadsheet model set up that works great except i users copy data from one unlocked cell to another the formatting of th other cell is copied as well (in this case the borders) -- Message posted from http://www.ExcelForum.com Simply no. XL 2003 allows selective locking, that is allows users to delete rows, insert rows, format cells, etc, but it still starts with a premise that the user cannot enter data in locked cells and unlocked one...

Formatting issue
I have the following formula in one of my cells: =IF(C5<>0,C6 &" Supplied by Marketing - Calculated value is " &C4,C4&" Calculated value") The values in C4, C5, C6 are all in dollar format but the output doesn't show in dollar format. See below: 7500 Supplied by Marketing - Calculated value is 8800 . Any way to get the format like this: $7,500.00 Supplied by Marketing - Calculated value is $8,800.00 Hi, =IF(C5<>0,"$"&C6&" Supplied by Marketing - Calculated value is "&"$"&C...

Formatting Cell problem
Hello guys, I have one frustrating problem. I copied a table from Amsouth Bank and pasted it into Excel. I then wanted to do some calculations. No matter how i formatted the cell whether Number or Currency the number always appeared on the left hand side and would not calculate. Number are supposed to be aligned right. Any suggestions. This is frustrating! Thanks! Hi - select an empty cell and copy this cell - select your imported numbers - goto 'Edit - Paste Special' and choose 'Add' -- Regards Frank Kabel Frankfurt, Germany "Jacob" <Jacob@discussions.mic...

Conditional format for image or figure
I am looking for a way to relate the format (filling colour) of an image or figure that is imported into excel. This would allow me to design simple maps with coloured codes for presentations of regional reports. Anyone? in Dutch: Ik wil dus bescheiden GIS kaartjes maken in excel zonder dat ik een veel te duur GIS programma moet opsnorren. (bijvoorbeeld de regio's met een goed verkoopcijfer groen, de regio's met verlies rood,...) Alvast bedankt ...

Excel needs more than 3 conditions in condititional formatting...
In Excel conditional formatting will only allow you to add 3 conditions to a cell. I need to have 4 conditions. Does anyone know a way to work around this. Has anyone tried visual basic? Hi here's a VBA solution i posted earlier as there's more than 3 conditions you can't use conditional formatting, however the following code pasted into the "sheet module" of the sheet - right mouse click on the sheet tab and choose view / code you should see on the top left of the VBE window your file name in bold (if not try view / project explorer) and the sheet that you were...

Outlook 2003 does not export all fields to any of the selected file formats
Outlook 2003 does not export all fields, it is missing the date and time field for messages. If there is a way please let me know. For what purpose are you exporting your .pst items? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Great Eyes asked: | Outlook 2003 does not export all fields, it is missing the date and | time field for messages. | If there is a way please let me know. "...

Changing format of number without changing the value
How do I add the % sign to a existing number without changing the value? Example: 1.68 should show 1.68% not 168? Select the cell & press Ctrl-1 Navigate to the Number tab Select Custom in the left hand panel In the Type edit box enter 0.00"%" Click on OK "sweetsue516" wrote: > How do I add the % sign to a existing number without changing the value? > Example: 1.68 should show 1.68% not 168? Sue, Percentage-formatted cells are based on 1 for 100%. It's only when you key in the percentage that it's converted, for example you key 1.68%, and it...

Need help formatting rows
Hello everyone, I am using excel 2007 I would like to know how to do the following if possible have column A add column B and put the total in column C and then put the amount of column C in column A of the next row. EX: 1+1=2 in row 1 then automatically have it put 2 in row 2 column A Thanks in advance if anyone can help. Bobby Cell C1: = +A1 + B1 Cell A2: = +C1 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "bobby02169" wrote: > Hello everyone, > I am using excel 2...

Convert text numbers to number format
I have received a file that has a date column. However, the column is reading as text. I need to change the column so that the date reads as a number format. Any ideas? -- montagu ------------------------------------------------------------------------ montagu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15227 View this thread: http://www.excelforum.com/showthread.php?threadid=314927 Right click and reformat as you want then click on cell and press F2 an enter to refresh data that should do the tric -- scottymellot --------------------------------------...

Re-format date (mm/dd/yyyy into dd/mm/yyyy)
Hi All, Could someone help me with the following: I have a range of dates which I want to convert from mm/dd/yyyy into dd/mm/yyyy, my formula (=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4)) works for most of the dates though in case of 1/12/2007 where the month only has one position (instead of 01) my formula takes "1/" for mm (instead of "01") Can one of you advice how to capture this issue in my (or a different) formula? Many thanks!!!! Rgds, Robert You could try: Data > Text to Columns > Next > Next >...

format group of rows based on condition
I've seen answers to similar problems, but nothing as complex as this: I have the following set-up (simplified) Type City Store actual Portland TB bid Portland TB actual Memphis AU bid Memphis AU bid Memphis AU I need to format in VBA (since this is over 3000 rows deep), in the following manner: Insert a row in between a change in the cities column. Then surround the grouping by city with a bold outline. Color the row with the bid gray and color the rows in between the cities blue. The sheet has...

datetime format issues when linking access to excel
Does anyone had problems with datetime issues when trying to link access into excel? using the database query option, it gives me an error of [microsoft][ODBC Microsoft access driver]invalid datetime format on column number 1 (date/time), and will not import the data. ...