HOW CAN i DEVELOP A FORMULA THAT RESTRICY SYMBOLS CHARACTERS TO B

I am trying to developt a formula that restrict symbol characters to be 
entred as data, help me!
0
Utf
4/28/2010 11:31:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
999 Views

Similar Articles

[PageSpeed] 19

This will limit data entry to alphanumeric characters:

=SUMPRODUCT((MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)>="A")*(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)<="Z")+ISNUMBER(-MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)))=LEN(A1)

Are there any other characters which you may wish to enter?

Steve D.


"Rodrjua" <Rodrjua@discussions.microsoft.com> wrote in message 
news:61E5CBBC-B00E-4BCF-B46D-8A9B5D247836@microsoft.com...
>I am trying to developt a formula that restrict symbol characters to be
> entred as data, help me! 

0
Steve
4/29/2010 9:12:43 AM
Reply:

Similar Artilces:

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Can't send e-mails from Outlook 2003 after SP1
After installing service pack SP1 for Office 2003, I can't send e-mails anymore. They are stuck in the outbox folder. The error message states that the connection to the server was interrupted. I did not change any settings and checking them confirms that everything is as it should be. Did anyone else experience the same issue and how did you resolve it? Thanks for your help Matthias See if you can manually move those messages from your Outbox and then check all your settings again. "Mattliusa" <anonymous@discussions.microsoft.com> wrote in message news:cb7401c48a5...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Can Line Chart Overlaps be Highlighted?
Can the overlap portion(s) of two lines in a chart be shown in a different color, without having to manually draw a shape? I would like to show the areas where Line A is above Line B as red, and the areas where it crosses below in blue - is it possible to do this without manually drawing in shapes? Hi, To some extent it depends how many times the lines cross. If it's just the once then you can use area charts to provide the shading. http://www.andypope.info/ngs/ng21.htm http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=590 This may help if the used one line as a base line...

Cannot enter an array formula
I'm trying to help a user on a toshiba laptop with array formulas. For some reason he cannot create an array formula. The formula evaluates on everyone else's laptop but his. He has Windows 98 and Office XP Professional. Has anyone encountered this? Ryan If he can't create it then you wouldn't know that it doesn't evaluate, so which is it? What's the formula? and can he actually create it but it doesn't appear to have the right answer. If you hit F9 does the right answer appear? -- Regards Ken....................... Microsoft MVP - Excel ...

time formula question
This formula works great if the ending time is before 0:00. =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)* {2;3;4})),"") $C$9:$C$11 is my starting time i.e. 20:00 $E$9:$E$11 is my ending time i.e. 04:30 How can I get this to work if A21 = 20:15? TIA, David Try this: =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"") HTH -- AP "David" <dfizer@r...

Help with formula #28
I have an excel spread sheet with formulas that work correctly, when I email them to another person and they open the attachment all looks ok. When they save the excel file to their PC and open it the formula cells now all have #Name in them. Anyone know what is causing this??? The error message gives us a clue: Excel can't find something - things to check: 1. Make sure the receivers all are using the same add-ins as the sender 2. Make sure that personal.xls is the same for the receivers as the sender 3. Make sure all defined names are the same -- Gary's Student "Play...

Can't sign in to 2004?
Now, another problem. I can no longer sign in with my hotmail and password if "use Money's online features that require a passport" even though I am signed on to MSN with that passport. I tried resetting the hotmail password for both MSN and Money 2004 but still can't get it to work. In microsoft.public.money, MrEKJ wrote: >Now, another problem. I can no longer sign in with my hotmail and password >if "use Money's online features that require a passport" even though I am >signed on to MSN with that passport. I tried resetting the hotmail password &...

Can't open files
Hello -- I'm sure this isn't the proper place to post my problem, but thought I'd start here anyways. Let me start off by saying that I'm extremely computer-stupid, so please forgive my ignorance. I'm running Windows Vista Ultimate, and yahoo email. For some reason, I'm unable to open attachments that have been sent to me, and saved to my computer. PLEASE HLP!! These are important documents that I really need to access. Thanks for your help -- It's not very informative to say you are not able to open attachments. What happens when you try? Is ther...

why I can not definition a variable in My Dll.
It's a dll support MFC INT __stdcall RemoterPrint() { AFX_MANAGE_STATE(AfxGetStaticModuleState()) int kkkkkkkk=0; INT booklet1 = 1; INT Pages1 = 0; INT m_FileID = ::InitUMFFile(szFilePathName);//Insert Breakpoint; ........... } I found the value is as follows kkkkkkkk 18072928 booklet1 -858993460 Pages1 -858993460 why ???? I am really puzzled. I added someting in stdafx.h #include <queue> #include<stack> using namespace std; "zhang" <makefriend8@163.com> д����Ϣ����:u1SbT%23FbHHA.4656@TK2MSFTNGP03.phx.gbl... > It's a dll support MFC ...

How can I change order of pages in a fold brochure in publisher
When I work on the pages on the screen it shows pages 2 & 3 and the page preview shows 2 & 3 but when I go to print page 3 comes out as page 5. Is your publication setup as a booklet? When you go to print are you checking current page or typing the page number? What version Publisher are you using? "JudieM" wrote: > When I work on the pages on the screen it shows pages 2 & 3 and the page > preview shows 2 & 3 but when I go to print page 3 comes out as page 5. "Mary Sauer" wrote: > Is your publication setup as a booklet? When you go to prin...

Ctrl that can have focus, and paint
Hi, Could anyone suggest to me a suitable control for use on a dialog that can get focus, recieve all of the keyclicks (including up / down etc), but also support full custom painting? I tried a CEdit but typing overrides my painting. - Mark R That's a loaded question. But you can inherit from CWnd, or you can even inherit from CStatic if you wanted. You will have to catch the WM_CHAR for some keys, and WM_KEYDOWN for others like the arrows. AliR. "Mark Randall" <markyr@REMOVETHISgoogle.ANDTHIScom> wrote in message news:uZtdckNGFHA.4004@tk2msftngp13.phx.gbl... &g...

how can i write arabic in Excel?
I already installed MS Office and i can write arabic in the word but when i try to use the excel, i can't write arabic, so please can you tell me what is the proplem and how i can solve it. I write arabic in excel done, have you already activate your win2000 or higher for the regional options> language > .... and you can add the arabic language for this option -- hni ------------------------------------------------------------------------ hni's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28451 View this thread: http://www.excelforum.com/showthr...

Can you have both Office 2004 and Office 2008 on your computer?
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel I was wondering before I install Office 2008 if I can keep Office 2008 on my computer as well. I know you can do this with the windows versions (office 2003 and office 2007). On 3/28/08 11:51 00AM, in article ee967d0.-1@webcrossing.caR9absDaxw, "mlejay@officeformac.com" <mlejay@officeformac.com> wrote: > > I was wondering before I install Office 2008 if I can keep Office 2008 on my > computer as well. I know you can do this with the windows versions (office > 2003 and office 2007). I'm s...

Exchange 2003 can't connect to SMTP on internal IP address
We have Exchange 2003 server no service packs. This has 2 IP addresses used for SMTP. We were looking to lockdown our Exchange server to only allow mail from our email provider. After setting this up we thought it would be better to do this at the firewall level, so we undid the changes we made. The changes were as follows: Created a global accept list Created a global deny list Created connection filter to an SMTP virtual server No reboot took place during this. When undoing the change we removed the IP list before the virtual SMTP server. This may have caused the problems out...

the pointer can not fixed
Hi, I have a problem with my pointer. when I type, the pointer usually move to another place; it doesn't fixed at the point that I am typing so I must continually fix it. Could you give me any suggestions? Thank you Are you using a Laptop with a touchpad pointing device? The usual reason for this problem is that the touchpad is oversensitive and even gently brushing near its surface is moving the pointer and/or simulation a mouse click. You can adjust the sensitivity or disable the click option in the Control Panel, Mouse applet. -- Terry Farrell - MSWord MVP &quo...

Help with formula trowing a #value! result
Hi I've downloaded a mortgage calculator from the web, being anewbie I'm stuck with the following issue: When the last payment comes down to 0 or near 0 like 0.03 cents all the formulas values return a #VALUE!, I want that the value be displayed as 0. In example the maximum payments are 431 monthly payments, lets say the last payment the client made was for 378, now after that paymet I need the value to be displayed as 0 up to row 431, because he has finished his payments. A formula is if(F378+D378<C378,C378-(D378+F378),"") EVALUATES TO------>...

In excel is there a way to pick out certain characters in a cell?
Ok my question is when I have a number in excel like in cell a1 there is the number 1267 and say in cell b1 I want the first character of that number to show up, so I want b1=1, c1=2, d1=6, and e1=7 is there are formula that I can put in each of those cells to pick out the first character, 2nd and so on or is this something I have to manually do everyday because I have a formula that is putting in the 1267 or what ever number each day but I need to seperate them out. You can use the MID() function, comme ca: =MID(A1,1,1) =MID(A1,2,1) etc. You could also use RIGHT() and LEFT() for the c...

Excell formula 02-21-10
I need a formula to calculate arrears payments on a spreadsheet The sheet has a target payment for collection each week a payment column and a remaining balance column weeks 1 to 52 I want the formula to count all arrears that are unpaid as a running total can anyone help What's the definition of "arrears"? Does it depend on today's date, or some other date, or something else? Define arrears, and where it can be found, and we can help you come up with the formula. Regards, Fred "tommy touareg" <tommy touareg@discussions.microsoft.com> wrot...

How can I add multiple source data data labels?
To be more exact, I require my original data labels (in an Area Chart in Excel) to remain visible, ie £250,444; in addition I require to show on the same drop line, either above or below this original figure, the Loan To Value amount, ie 48%. I am sure this is possible, however I am unable to discover the correct process to enable this eventuality. Hi, First you need to create a concatenated formula in the spreadsheet, then you need to use a custom add-in to use those cells a data labels. Assume you 250,444 is in column B and the 48% is in column C then in column D enter thr...

how can i disable save i need save as only
i have a report that others use. the main bady of the report is protected against change. but i need to disable the save so they only use save as. is this possably David, Save the workbook as a template, and the user will be prompted for a name when they use save or save as, unless they specifically save the file as a template as well. HTH, Bernie MS Excel MVP "David128" <David128@discussions.microsoft.com> wrote in message news:5708134D-2619-4A82-9970-7ED160028F45@microsoft.com... >i have a report that others use. the main bady of the report is protected > again...

can't send url from ie6, blocked by outlook
IE6 from File, Send, Link by email, I get a notification that Outlook has blocked it. anyone know how to undo this? ...

Only see name box and formula box where is the rest?
Hi, I accidently unchecked an option because of which now when I open excel all i see is the name box and the formula box and the worksheet. I do not see the menu nor the toolbar options. I have tried the Alt+T C but it doesnot work. How do I make the toolbars and the menu show up again. thx view/toolbars/standard and formatting Can you see the File / Edit / View menu bar? "vik" wrote: > Hi, > > I accidently unchecked an option because of which now when I open excel all > i see is the name box and the formula box and the worksheet. I do not see the > menu n...