Count "," in a cell

Dear expert,

Is it possible to count "," in a cell please?

I used this ... but does not work

=COUNTIF(FO93,",")

Say below ... can it be solved?
2,5,3
22,25,5
5,2,3,4



0
Utf
6/3/2010 11:03:38 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
788 Views

Similar Articles

[PageSpeed] 33

Try this…

=LEN(FO93)-LEN(SUBSTITUTE(FO93,",",""))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Elton Law" wrote:

> Dear expert,
> 
> Is it possible to count "," in a cell please?
> 
> I used this ... but does not work
> 
> =COUNTIF(FO93,",")
> 
> Say below ... can it be solved?
> 2,5,3
> 22,25,5
> 5,2,3,4
> 
> 
> 
0
Utf
6/3/2010 11:18:05 AM
Try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",))

-- 
Jacob (MVP - Excel)


"Elton Law" wrote:

> Dear expert,
> 
> Is it possible to count "," in a cell please?
> 
> I used this ... but does not work
> 
> =COUNTIF(FO93,",")
> 
> Say below ... can it be solved?
> 2,5,3
> 22,25,5
> 5,2,3,4
> 
> 
> 
0
Utf
6/3/2010 11:18:05 AM
Reply:

Similar Artilces:

Use of '=AND( COUNTIF(A:A, B1))' to check if a cell is in a column or range
Hello. Can someone please help, I'm using Excel 2003 and I wondered if this is an efficient way of checking whether an item is is a column or part of a column of (length 5000 rows) or not: - =AND( COUNTIF(A:A, B1) ) It seems like an odd use of the AND function as it only has one parameter but it returns a TRUE or FALSE as you would expect. I don't want to count the number of occurences, I just want to know if the value is present in the column but I can't seem to find an alternative to COUNTIF. The IF and FIND commands don't seem to apply here. Thanks for any help given. H...

Find a vlaue, shade another cell
I would like to search column c in an excel sheet for any number that starts with '46'. all the numbers in the column will be 7 digits long (eg. 4634567) once found I would like to turn the font blue in the cell 10 columns to the right of this 46 cell. the macro should loop down column c until it can find no further values. any ideas if this is possible? Hi, Try this Sub Sonic() lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row Set MyRange = Range("C1:C" & lastRow) For Each c In MyRange If Left(c.Value, 2) = "46" Then ...

Select item from dropdown list, item code is displayed in cell
I need help with a spreadsheet problem. I created a timesheet for users to track their hours with an in-cell drop down for some of the values. Currently, an user is able to use the in-cell dropdown to select a time off code and the result is displayed in the cell. However, I would like to have the user to see the full-name on the in-cell dropdown, but have the abbreviated code display in the cell when selected. I have been unable to figure out how to do this, so any help would be great! I am trying to stay away from extra Excel components or macros, as I like to keep this sheet easy for the e...

All Cells in Excel are locked, what's wrong?
I have a huge problem! All the cells in Excel is locked. I cannot edit the cells at all. And this is with fresh started sheets as well. I can't change anything. I could yesterday, until some point - where it just didn't work any more. What can I do? I really need help... Check under <tools>, does it say protect or unprotect sheet "Samuel" wrote: > I have a huge problem! All the cells in Excel is locked. I cannot edit the > cells at all. And this is with fresh started sheets as well. I can't change > anything. > > I could yesterday, until som...

Execl2002: Editing via F2, cell in light blue, Enter inserts new line
Hello, suddenly when I edit a cell via F2, the cell is highlighted in a color like light blue. When I press Enter a new line is inserted, it's not possible to end editing with Enter. Any hint? Thanks and greetings Udo Hi Do you see EXT on the bottom bar of Excel? If so, you are in Extended mode. Press F8 to clear. -- Regards Roger Govier "Udo" <WeikEngOff@aol.com> wrote in message news:1191014455.445421.159380@19g2000hsx.googlegroups.com... > Hello, > > suddenly when I edit a cell via F2, the cell is highlighted in a color > like light blue. When I p...

Subtotal
OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

Counting unique items, disregarding thier annexs
"THIS IS A rePOST WITH SOME CORRECTION" hi all, seeking help please. The column A1:A100 contains items and also empty cells like facil.1,beauty.2,facil.,facil.3,beauty.4 and so on.... Looking for a solution where on counting items will yield 2 only (for "facil" and "beauty") and not 5 as each entry is unique, as the flowing formula do: =sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1)) many thanks for any help. On Wed, 15 Sep 2004 03:23:03 -0700, "excelFan" <excelFan@discus...

Count, Sum, Index,Match or other formula needed
Using Excel 2003: One row is a set of numbers. I need to match that to another worksheet and find certain results in it. Here's the trick. This second worksheet will have these numbers over and over again with either Yes or No in the column I want returned. Example, number in cell one is shown 10 times in the second worksheet sometimes with yes and sometimes with no in the column I want back. The result need to find the number in the 1st worksheet and return a No if any of the matching in the 2nd worksheet contain a No in the column I want returned even if sometimes...

Updating linked cells within a workbook, from worksheet to workshe
(Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at o...

How do I convert last name, first name in cells to first name las.
Cells contain names in last name, first name order. I wish to convert to first name last name (no comma) without retyping. Thanks Assuming your data is in column A. Select the names in Column A. Make sure that Columns B and C are blank. In Excel 2003, click on data, text-to-columns and choose delimited and comma. The last name of the first entry will be in A2 and the first name will be in B2. In C2 type =B2&" "&A2 to concatenate the name with first name first and then copy down the formula in C2 as far as necessary. As a last step be sure to copy Column C, go ...

how do i link cells so that when typing in an item, the price app.
Hi there, I'm trying to create a quotation template which enables me to enter in an item in one cell and its corresponding price appear in the next cell? Is this possible? I have created a spreadsheet of Items and prices (of which there are going to be over 1000) as a refernce point but am unsure how to link these for automatic entry into the quotation. Anyone know how to do this? Cheers julie Hi Julie! You need to build a table of the items and prices. For example, on sheet2 in column A you list the items and in column B you enter the corresponding price. Assume that table...

How to get notified when user inserts cells, rows, or columns in W
I'm making a client to Excel that keeps references to cells on worksheets. These references must be updated if the user inserts cells to the left or above the referenced cells. I have not been able to find this information, neither in the Range that accompanies the Change notification nor in any of the Worksheet, Workbook, or Application properties. Can anybody help me? Using the Worksheet_Change event, you could probably evaluate the row and column values of the target cell against the criteria cell: If Target <> (Reference Cell) Then If Target.Row <=( Refere...

Formula works in some cells, doesn't in other
I have a multi-sheet 2003 workbook. Sheet 1 is a summary that displays data from the other 4 sheets, the name of one of which is "Northeast" (though experimentation proves that it doesn't matter what the sheet is named) On my summary sheet this particular sheet is referenced in row 4. Column A is nothing but the text "Northeast", the formulas in each of the next columns are as follows (without the B: C: D: E: and F:) B: =SUMIF(Northeast!C:C,">0",Northeast!B:B) C: =SUM(Northeast!C:C) D: =SUM(Northeast!D:D) E: =1-B4/ABS(C4) F: =AVERAGE(IF(Northeast...

no cells, grey screen, all tabs greyed out
I just opened my spreadsheet (xlsx file), no data or cells are present and all the tabs are greyed out. The screen (where cells/data s/b present) is just a grey blank screen. On the very top where the file name s/b in the border, it just says Microsoft Excel. What has happened? Can I recover my data? You need to open a workbook file (the Office icon is not greyed out) or start a new (use shortcut CTRL+N) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "John" <John@discussions.microsoft.com> wrote in message news:0FDC...

Reverse Text in Cell before and after comma (not just Word1, Word2
I've seen some workarounds for doing Smith, John to John Smith But I am looking for Word1 MaybeWord2-3, WordA MaybeWordB-C i.e. just put all the words after the comma in front and vice versa e.g. Assistant Manager, Building Services=Building Services Assistant Manager Manager, Building Services = Building Services Manager Assistant Manager, Building = Building Assistant Manager Is there an 'easy' way to do this with cell functions vs VBA? Thanks in advance! I sometimes break this into smaller pieces. Say your data is in column A (A1:A3) I'd...

min,average>0 if 52 cells read zero
I have 52 cells w265 that all read zero until data is entered this means one cell in each worksheet example Week 1 w265, week 2 w265, week 3 w265 to week 52 w265. on a seperate work sheet im trying to caculate min & average for efficiency but when data is entered for min i get zero because of other cells that read zero or the average is incorrect too low I have to have zero,s in place on the 52 worksheets but i don,t want io include them when calculating min or average on seperate work sheet can some please help Mike, The easiest thing to do is to completely empty all you...

Cell reference, dragging across
My question is pretty simple, and if anyone knows the answer I would appreciate a response. If my cell reference is =b7/c8, and I want to drag the reference cell across and change the cell number but keep the letter constant, is that possible? Thanks, Jeff Grossman ...

Change Number to Text , Case error when cell is String and format is General
Hi All How to check the cell is text or number ? When Cell is string, Case Excel close and recoveing. Sub FormatNum2str() '~~ 2010/02/18 Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim strVal As String If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ic = 1 To iCols For ir = 1 To iRows With Application.Selection.Item(ir, ic) If .NumberFormatLocal = "Gene...

HLookup, multiple tables and range names in other cells
Hello, I am beginning to discover Excel with the aid of various textbooks but I am stumped on one problem and don't wish to embark on VBA custom functions unless I have to. I am intending to use HLookup with multiple tables (over 15) in the same worksheet. So here is my problem, =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) The 'table_array' part can be a range or the name of a range, if I have a cell that is calculated to produce the name of a range eg. an INDEX and MATCH formula, how can I get 'table_array' to accept this without errors? eg.=HLOOKUP(A...

how to print defined cell names with comments, not numbers?
My overall task is to print comments at the end of the sheet that display the defined cell name instead of the cell number. Problems: How do I define all of the intermediary cells based on the row and column headings, and then how do I print the comments to display the defined name, instead of the number. For example, I would like my print out to display: Cell: "Defined Name" Comment: Instead of: Cell: B2 Comment: ...

When I filter data, sometimes I do not get row count retrived
When you are using auto filter on a worksheet and then you pick a valu to filter, you get the list of rows that satisfy that criteria. Also in the status bar, you get the number of rows retrived as a result o this filter. Example you will get "3 of 25 rows found" or somethin like that. Sometimes I do not get that message, I just get a messag called filter mode. Why does this happen and how can I correct this? Thank you -- Message posted from http://www.ExcelForum.com If your worksheet has any formulae on it they automatically calculat after filtering thereby suppressing the messa...

AutoSave without editing, or calculating cells without change
I am pulling data from another place which gets updated on save, however not just save, something on the sheet appears to need to be updated. I have written a Module which saves the sheet on a pre defined timed interval. The procedure is working (without editing the sheet). But my data will not update unless I manually change a cell anywhere on the sheet, then at the next timed interval of Save, all my tags are updated. How do I Change a cell (from within my code) each pass through the timer. I beleive if the cell automatically updated right before the save command, then my data ho...

Cells(#,#).Value conversion
Hey I want to grab a serialized date and turn it into an integer in my vba what is the proper conversion for this ? exampel: Dim startDate As Integer Dim endDate As Integer startDate = ActiveRow.Cells(1, 2).Value endDate = ActiveRow.Cells(1, 3).Value Hi Alexandre, In addition to Bob's suggestion, change: > startDate = ActiveRow.Cells(1, 2).Value > endDate = ActiveRow.Cells(1, 3).Value to startDate = Cells(1, 2).Value endDate = Cells(1, 3).Value --- Regards, Norman "Alexandre Brisebois (www.pointnetsolutions.com)" <alexandre.brisebois@gmail.com>...

different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1. I would like to have cell B1 display: Completed 2/17/07 If I use the formula ="Completed "&A1, it displays: Completed 39130 Is there another way to do this? To Excel, dates are just numbers, so you need to tell it to convert the number (date) to text how to display that text. Try something like this: ="Completed "&TEXT(A1,"MM/DD/YYYY") Does that help? *********** Regards, Ron XL2002, WinXP "Matt" wrote: > Say the date that a certain event occurs (2/17/07) is in cell A1....

Specifying source campaign in Opp., not counting as campaign respo
General usage question regarding campaigns. If we create a campaign, then specify that campaign as the source campaign for an opportunity, that opportunity/account is not then listed as a response to that campaign. So when we run a Campaign Performance report, no responses are listed. Do you have to use the normal method of entering a response within the campaign? I'd think that if you can associate a campaign within the opportunity, that linkage would reflect in a Campaign Performance report. Thanks!! ...