Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Instances of a Specific Character
here's what I am trying to do - I am trying to split up a cell based
on it's contents. While the data is in the same order in the column and it is delimited by a space, it doesn't work to delimit it by spaces or by number of charatcers. The problem is that between the delimiters there may be 1 or 2 words - separated by a comma and the data may be of variable length. Is there a way to Count the Instances of a Specific Character - for example, is there a function to count the number of spaces in this sentence? Or the number of x's? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Instances of a Specific Character
=len(a1)-len(substitute(a1,"x",""))
will count the number of lowercase x's in A1. (=substitute() is case sensitive) If you wanted the total number of x's (upper or lower): =len(a1)-len(substitute(upper(a1),"X","") or =len(a1)-len(substitute(upper(a1),upper("x"),"") (I'm always afraid that I'll forget to make that "X" upper case, so I like to use the =upper() function to remind me.) And if you have lots of spaces that you want to get rid of: =trim(a1) will remove leading, trailing and change multiple internal spaces to a single space: ---qwer----asdf-qwer---qwer------- (- = space) will become qwer asdf qwer qwer after =trim() Commish wrote: here's what I am trying to do - I am trying to split up a cell based on it's contents. While the data is in the same order in the column and it is delimited by a space, it doesn't work to delimit it by spaces or by number of charatcers. The problem is that between the delimiters there may be 1 or 2 words - separated by a comma and the data may be of variable length. Is there a way to Count the Instances of a Specific Character - for example, is there a function to count the number of spaces in this sentence? Or the number of x's? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Instances of a Specific Character
On Mon, 10 Aug 2009 14:45:52 -0700 (PDT), Commish
wrote: here's what I am trying to do - I am trying to split up a cell based on it's contents. While the data is in the same order in the column and it is delimited by a space, it doesn't work to delimit it by spaces or by number of charatcers. The problem is that between the delimiters there may be 1 or 2 words - separated by a comma and the data may be of variable length. Is there a way to Count the Instances of a Specific Character - for example, is there a function to count the number of spaces in this sentence? Or the number of x's? Try this formula: =SUMPRODUCT(--(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)="x")) The result should be the number of x's in cell A1. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the times a specific character appears in a cell | Excel Worksheet Functions | |||
How do I count occurances of a specific character in a range? | Excel Worksheet Functions | |||
counting instances of specific times in fields with date and time | Excel Worksheet Functions | |||
Count Instances | Excel Discussion (Misc queries) | |||
HOW to COUNT THE FREQUENCY of specific CHARACTER WITHIN a CELL? | Excel Discussion (Misc queries) |