Identical character count calculation (DIADE)
Alcyde
-
Alcyde -
Alcyde -
Bonjour,
I want to find a formula in a series of letters example: MMMOOMMOM
to identify the number of times two m's touch.
here the answer would be 3, but the formula I am using identifies 2. if for example the sequence is: MMMMMMOOOMMMOO, it identifies 4 while the answer is 7. For the rest of my table, this formula works for every time an M and an O touch for example. The problem is that when I have a long sequence of M's, the formula only counts the number of times it sees MM in the cell, not the total number of times two M's touch.
=SUM(LEN(A14)-LEN(SUBSTITUTE(A14,"MM","")))/LEN("MM")
Do you have a solution?
Configuration: Windows 7 / Mozilla 11.0
I want to find a formula in a series of letters example: MMMOOMMOM
to identify the number of times two m's touch.
here the answer would be 3, but the formula I am using identifies 2. if for example the sequence is: MMMMMMOOOMMMOO, it identifies 4 while the answer is 7. For the rest of my table, this formula works for every time an M and an O touch for example. The problem is that when I have a long sequence of M's, the formula only counts the number of times it sees MM in the cell, not the total number of times two M's touch.
=SUM(LEN(A14)-LEN(SUBSTITUTE(A14,"MM","")))/LEN("MM")
Do you have a solution?
Configuration: Windows 7 / Mozilla 11.0
Related links:
- EXCEL Calculate the number of YES and NO
- Count identical characters in Excel cell
- Calculation of the number of months (even incomplete) between two dates
- Count occurrences of a word in a range of cells
- Excel table calculating number of working days negative number
- Counting the number of women and men in Excel
2 answers
-
By VBA, the simple code below calculates what you are looking for:
Sub test()
m = 0
For i = 1 To Len(Cells(1, 1).Value) 'in A1 the cell to analyze
If Mid(Cells(1, 1).Value, i, 1) = "M" And Mid(Cells(1, 1).Value, i + 1, 1) = "M" Then
m = m + 1
End If
Next i
Cells(1, 2).Value = m 'we put the final result in B1
End Sub-
-
It's a macro, so to use it you need to enable macros in your Excel and paste this code into the code of the relevant sheet.
In short:
1) enable macros (Google search if you don't know how to do it)
2) in your document, access the VBA code (Alt + F11) then to the code of the sheet that concerns you (mini window at the top left, double-click on the sheet)
3) paste this code
4) return to Excel (Alt + F11 again or Alt + Q)
5) run the macro (Alt + F8 and choose the name of the macro, which is "test" in this case, or create a button assigning the macro to it)
It's much more efficient than using a formula, but it does require knowing how to use macros :s
-
-
By formula, I found nothing better than an embedding of SUBSTITUTE by listing the possible series of M in descending order.
An example that only works if the longest series of M in a row is 5 M (like in your example):
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14;"MMMMMM";"XXXXX");"MMMMM";"XXXX");"MMMM";"XXX");"MMM";"XX");"MM";"X");"M";"");"O";""))
-> you indeed find 7 for MMMMMMOOOMMMOO in A14-
-
Eheh I'm not too optimistic about this, one solution could be to refer to other cells where part of the SUBSTITUTE functions are already nested, that allows you to exceed the limit but it's rather ugly..
My advice:
- Either you have a fixed list of series to analyze, in which case you identify beforehand the maximum sequence of M and work with formulas as best as you can
- Or your list is variable, in which case I recommend using VBA (the macro I coded in the previous comment)
There you go, I can't help you more, except on the use of the macro (the possibility to trigger it automatically before saving for example, or when modifying a cell, etc.) -
-