Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the number of instances of a string within another string
I'm sure there is an easy way to do this; I just haven't had to do it before
so I'm not sure what the most elegant solution is. I have a column of values, and within each cell, there are an unknown number of commas (e.g. "323,76,12,43"). I need to identify (for each cell, in the next column over) how many commas are present. The basic instr or match type functions (I think) only provide the first match location, not a total number of matches. Maybe something like the opposite of REPT, only something that ignores all the other text and just gives the count of the target string that is already repeated in the cell. Any suggestions on a preferred approach? Thank you, Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the number of instances of a string within another string
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Keith R" wrote in message ... I'm sure there is an easy way to do this; I just haven't had to do it before so I'm not sure what the most elegant solution is. I have a column of values, and within each cell, there are an unknown number of commas (e.g. "323,76,12,43"). I need to identify (for each cell, in the next column over) how many commas are present. The basic instr or match type functions (I think) only provide the first match location, not a total number of matches. Maybe something like the opposite of REPT, only something that ignores all the other text and just gives the count of the target string that is already repeated in the cell. Any suggestions on a preferred approach? Thank you, Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the number of instances of a string within another string
Try this:
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Biff "Keith R" wrote in message ... I'm sure there is an easy way to do this; I just haven't had to do it before so I'm not sure what the most elegant solution is. I have a column of values, and within each cell, there are an unknown number of commas (e.g. "323,76,12,43"). I need to identify (for each cell, in the next column over) how many commas are present. The basic instr or match type functions (I think) only provide the first match location, not a total number of matches. Maybe something like the opposite of REPT, only something that ignores all the other text and just gives the count of the target string that is already repeated in the cell. Any suggestions on a preferred approach? Thank you, Keith |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the number of instances of a string within another string
Excellent, thank you Bob and Biff!
"Bob Phillips" wrote in message ... =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Keith R" wrote in message ... I'm sure there is an easy way to do this; I just haven't had to do it before so I'm not sure what the most elegant solution is. I have a column of values, and within each cell, there are an unknown number of commas (e.g. "323,76,12,43"). I need to identify (for each cell, in the next column over) how many commas are present. The basic instr or match type functions (I think) only provide the first match location, not a total number of matches. Maybe something like the opposite of REPT, only something that ignores all the other text and just gives the count of the target string that is already repeated in the cell. Any suggestions on a preferred approach? Thank you, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the appearance of a String in a Row | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
Counting number of time a character appears in a string | Excel Worksheet Functions | |||
Countif function for instances of text string contained | Excel Worksheet Functions | |||
Countif function for instances of text string contained | Excel Worksheet Functions |