Data validation question #2

I'd like to create data validation in a cell that prevents
user's from entering any commas, but allowing all other 
characters.  This spreadsheet is exported to a comma delimited
file, and extra commas cause problems.  I looked at the examples
in help, and couldn't find anything preventing a character from 
being entered into a cell.  Any ideas?

-Thanks

0
itfred (2)
12/7/2004 12:18:19 AM
excel 39879 articles. 2 followers. Follow

1 Replies
587 Views

Similar Articles

[PageSpeed] 34

A couple of ways:

=ISNUMBER(SEARCH(A1,","))
or
=COUNTIF(A1,"*,*")=0
or
=LEN(A1)=LEN(SUBSTITUTE(A1,",",""))



Fred wrote:
> 
> I'd like to create data validation in a cell that prevents
> user's from entering any commas, but allowing all other
> characters.  This spreadsheet is exported to a comma delimited
> file, and extra commas cause problems.  I looked at the examples
> in help, and couldn't find anything preventing a character from
> being entered into a cell.  Any ideas?
> 
> -Thanks

-- 

Dave Peterson
0
ec357201 (5290)
12/7/2004 12:53:49 AM
Reply:

Similar Artilces:

Re: data manipulation #2
Thanks Max, What I am trying to do with this is for every service date that ha more than one (1) panel associated with that date.........list all o the data on the next line with the new panel. For example, if we too the following data : Prefix, Service date, DOB, Panel1, Panel2, Panel3, Panel4, Panel5 97 May97 10/2/1974 ANS AN3 MET PCR CFE What I would like the data reformatted as is as follows : 97 May97 10/2/1974 ANS 97 May97 10/2/1974 AN3 97 ...

Move Data Table?
How do I move the data table from under the chart to the right side of it? I can't seem to move anything on the chart around. :O( ~CJ CJ, Unfortunately there is no way to move the data table. If you are using an embedded chart, you might consider eliminating the chart-generated data table and using worksheet cells instead. If you apply worksheet formatting to match the chart that includes the new data table, you can create an effect that the data table is embedded within the chart. ---- Regards, John Mansfield http://www.pdbook.com "Charisma Riley" wrote: > Ho...

Redefining ranges #2
This is an awfully long question (sorry) but in order for someone to know where I am at, I need to explain, hopefully. A few days ago I asked the following question. I used the following code that will not allow an end user to close a spreadsheet until all specified cells are entered. Private Sub WorkBook_BeforeClose(Cancel As Boolean) If Application.WorkSheetFunction.CountA(Sheets("Sheet1") _ .Range( "Customer" ) <8 Then MsgBox " You must fill in all cells" Cancel = True End If End Sub Now it works okay with one sheet but we have hun...

Question about CSocket::Receive()
I have a class derived from CSocket. I have a vague recollection that I only get one shot at calling Receive() in my override of OnReceive(). What I mean by that is that if there's 2k of data to be read but I only read 1k when I call Receive() then the other 1k gets lost. Or can I call Receive() more than once within OnReceive() but after I exit OnReceive() I lose any data that didn't read? I can't remember where I've read this or something along those lines and I can't see it in the documentation. It may be that I'm completely mistaken and it's just my ...

Data Tables and Conditional Formatting
I have a spreadsheet that is set up with currency values and negative currency values display in red text. When charting these values, the data table is all black text. Is there a way to get the negative currency values in the data table (under the chart itself) to display the in red text while still displaying the positive currency values in black text? I am running Windows XP and Excel 2003. Thanks for your help. Rich ...

question on multiple email domains
what options do I have to accomplish the following: hosting email for 2 email domains on same exchange 2003 server (domainA and domainB). DomainA and domainB represent different divisions of one company but many users will be doing work for both divisions and therefore need to have the 2 email addresses. Here is the critical part: when email sent to say, rsmith@domainA is recevied by rsmith and rsmith replies, it must come 'from' rsmith@domainA, and when email is sent to rsmith@domainB and rsmith replies it must come 'from' rsmith@domainB. This is because it is the same ...

Sorting Worksheets #2
Hi.. is it possible to sort worksheets alphabetically? I have a workbook containing 50 or so workbooks, and it would be good if I could sort them automatically many thanks How about a macro? Chip Pearson's: http://www.cpearson.com/excel/sortws.htm David McRitchie's: http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets pepperds wrote: > > Hi.. > > is it possible to sort worksheets alphabetically? I have a workbook > containing 50 or so workbooks, and it would be good if I could sort them > automatically > > many thanks -- Dave Peterson...

sync issue with public / private calendar. #2
Hi all, came across a strange action regarding public/private calendars that I do not think should occur. When userA creates a meeting in their private calendar and invites public folder calendar to the meeing, Life is good, it's in both UserA's calendar and PFCalendarB's calendar. however if When User C just opens the PFcalendarB appt, it automatically puts it into UserC's private calendar. Other users say that the public folder appoint automatically shows up in their private calendar. I do not think this is expected behavior. Is there a way to prevent this? Thanks Fran...

Mailbox Resources #2
Were running Exchange 5.5 SP4 Standard Edition. Were aproaching the 16 gb limit of the Private Information Store so were carefully monitoring mailbox resources. If I expand the Private Information Store in the Exchange Admin program and choose "Mailbox Resources" it shows all the mailboxes and I can arrange them by total K to see who has the largest mailbox. If I go up a level to "Private Information Store" and look at the properties, I can then go to the "Mailbox Resources" tab and again arrange by total K. Now at the bottom of the list instead of the user with t...

Simple pie chart question
I'm a teacher trying to make a pie chart illustrating a part of a whole as a percent. I have two columns, one column (A1-A10)contains the total possible points for assignments 1 -10 the other column (B1- B10)contains the points earned for assgnments 1-10. the cell below each column has a formula that displays the sum of cells X1 - X10. So if a student has earned 75 points out of a possibe 100 you will see 100 in cell A11 and 75 in cell B11. OK. My question is how do I make a pie chart that will display something that looks like it is 75% one color and 25% another. I keep getting ...

questions on redeployment tool to copy production to test
In our environment, we have production and test CRM systems on two different domains/forest. Both have CRM running right now. Our objective is to refresh the CRM data in the test environment from Production. 1. The guide says that we need to create a new OU before starting the deployment tool. Do we need to create a new OU for our test system again. The OU is already present. What do we need to do to the OU and the users in it already in the test environment? 2. After the deployment wizard step in the document, the next step is Install Microsoft CRM. Do we need this step as it is alr...

Two "getting started" questions from a newcomer.
Two questions 1. When I open Outlook, it doesn't automatically bring up my dialer to access my mail, as does Outlook Express. In other words, whenever I click on my Express shortcut, my dialer appears, permitting me to dial up and check my mail. But when I click on the Outlook shortcut, the program opens, but then I have to call the dialer up separately 2. When Outlook opens, I always see this window labeled Microsoft LDAP Directory. It doesn't matter whether I click "okay" or "cancel," because nothing seems to happen. How do I stop this window from popping ...

Coulmn widths different between 2 files?
Hello, I am trying to set column widths in one file to match that of another file. I set the columns to the same size (40) in each file. However, they end up different widths! The width in the first files says '40.00 (245 pixels)', while the other file says '40.00 (325 pixels)'. Why are the pixels different between the two!? Please help! Notes - Both files have the same: 1) magnification 2) font type and size 3) page setup printer resolution 4) saved as excel workbook 2003 format bump "Mr. Etec" wrote: > Hello, > > I am trying to set column widths ...

exchange rate #2
Hi, Book1 has 12 sheets for 12 months. Another sheet is named "c_rate" for currency rates. Column B has the dates of the month and column C has the excahnge rate for that day (from $ to NIS). This is for the twelve months. In sheet for January at column A, I have dates and they are not assending order. What formula should be in cell BU10 (sheet of Jan) so that it reads the date for cell A10 and multiply the value of exchange rate for that day from sheet "c_rate" and put it in cell BU11. ...

Multivari Chart #2
Has anyone put together a procedure to create a Multivari chart in Excel? If so, I'd greatly appreciate seeing it. Thanks, Barb Reinhardt Hi Barb - I had to Google to see what a Multi-Vari chart was. I used to do them all the time, without knowing there was a fancy name for them. If you have some sample data you want to look at, post it, and I'll see if I can help. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Barb R. wrote: > Has anyone put together a procedure to create a Multi...

Question about accelerator keys
Hi all of you, When I assign an accelerator key to a button name "Show options" an assign the accelerator key as 'o', the 'o' in 'Show' is underlined. I there anyway to tell VBA that I want the 'o' in 'options' underlined? In this particular case, maybe I can make o in options, a capita letter and get away with it but in general can we select the nt occurence of the letter to be underlined? Thanks. Ra -- Message posted from http://www.ExcelForum.com Hi! AFAIK Excel selects the first occurrence of the chosen symbol. Al -- Message poste...

Passwords #2
When I receive mail, Outlook 2002 asks for my password, even though I have saved it. I only have 1 account and I've upgraded to Service Pack 2. Anybody have any ideas? ...

I Lost my public folders #2
This morning I had a public folder store that was working well for all my users. The only problem was that just about everybody had full permissions to read and write to every folder. I decided it was time to tighten down the security permissions. I went into ESM -> Folders -> Public Folder Tree and prceeded to edit the permissions to what I thought they should look like. Unfortunatly, I can't remember what I did to the top folder group, but after I clicked apply, the Public Folder Tree dissappeared from ESM. I'm sure its not deleted because if I try and create a new tree wi...

Frx Error Message #2
Adding FRx 6.7 to a new computer after installing MBS GP 8.0 & SP2. Error message "FRx Document Image Writer failed. Error 2" received twice when installing FRx 6.7. Install program proceeded and finished. Went ahead and ran FRx 6.7 SP3 and it installed with no errors. Operating system is Win XP Professional SP2. Any ideas on what error message means and resolving any problem? Thanks! FRx Document Image Writer is a customized version of the Microsoft Office Document Image Writer. We use it for printing and previewing of report books in FRx Report Manager. I'm ...

Database error in POS 2.0
I just started getting this msg "A problem occurred while checking your store database." Now it won't let me back up. This has been going on for a week - my [stupid] salespeople just told me. Now, it's too late to restore from backup w/o redoing a week's worth of receipts. Nightmare. Anybody know a way out of this??? How old is your database (and how big)? Before getting into the nuts and bolts of the database, try connecting to it from the store admin and reindexing it. -- -Jerry JR Data Inc. "MrBill" wrote: > I just started getting this msg &quo...

Anyone developed an Excel sheet for Cost of Quality? #2
...

Updating a chart automatically #2
I am using Excel 2002, and I need to modify a chart so that it update automatically. The way the spreadsheet is laid out is that it uses row of months at the top, a bunch of detail rows below that, and tw different total rows at the bottom. I'm only concerned with the month row and the two total rows. In the chart (an embedded line chart on separate sheet from the data), the months are the x-values and th total rows are the two data series. The amount of information bein represented is getting past two years' worth, and as time goes on, mor and more data will be squished into the sa...

Years of Service Calculation to 2 decimal places
Hi there In my database, I have used the following calculation to determine a person's years of service. YearsOfService: DateDiff("yyyy",[HireDate],Date()) -IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0) A t the moment, the result is rounded to the nearest whole number. I would like to see at least 2 decimal places. At the moment, if someone has been with the company less than 1 year, I get 0 years. I would like to see if it's .75 years or .5 years, etc. Thank you in advance for your help. forest8, Under properties f...

subtotaling worksheets #2
very vague, I'm afraid ... any ideas -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=27025 I'm not sure whether I am intrepreting what you want correctly. I thin you want to create subtotals on each page and then copy the subtotals a the bottom of the page. For each worksheet you could: 1. create the subtotals - use absolute cell references, specify Jan-De columns 2. loop through the rows to f...

2 dimensional array within function
Hello, I have a 2d array defined as follows: typedef CArray<int,int> arLineInt_t; typedef CArray<arLineInt_t,arLineInt_t> ar2DInt_t; ar2DInt_t Matrix2D; How can I pass that array to functions. I tried some things, but I can not realy access the elements of the array within the functions. Thanks Frank "Frank" <frankdeicke@gmx.de> ha scritto nel messaggio news:eXAQfbJaIHA.5208@TK2MSFTNGP04.phx.gbl... > I have a 2d array defined as follows: > > typedef CArray<int,int> arLineInt_t; > typedef CArray<arLineInt_t,arLineInt_t> ar2DInt_t;...