Count the number of specific characters in a cell

[JayReddy]

Good Day,

I am trying to write a formula to count the number of specific characters in a cell. This has proved successful to a degree utilising a LEN formula that I have listed below:

=LEN(A1)+(1)-LEN(SUBSTITUTE(A1,"+",""))

With the above formula I am counting how many times the plus (+) symbol appears in the cells in column A and then adding the value of 1 to the total and this is calculating correctly.

The below example shows the correct count of characters in column A utilising the above formula:


[TABLE="width: 212"]
<tbody>[TR]
[TD]Column A - Data[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1+2+2.4+2+6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1+2+3+4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1+4+7+6.5+7.86+3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Now I want to expand the formula to include an additional character. This would be the minus (-) symbol.

I need the formula to count how many times either the plus (+) or minus (-) symbol appear in a cell in column A and add these two counts together and then add an additional value of 1.

The below example shows the desired total count of characters in column A:


[TABLE="width: 212"]
<tbody>[TR]
[TD]Column A - Data[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]5+4+5+3.0-6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8-3+6-3-1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]9+4+2-2-2-1[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way to expand my existing formula to count more than one character (i.e. the plus (+) and minus (-) symbols together)?

Any assistance in this regard would be greatly appreciated.

Best Regards,
Jay

 

[bristolrob]

Re: How to count the number of specific characters in a cell

Fluff
Perfect. Works exactly as i wanted and thanks very much for the solution

 

[Peter_SSs]

Re: How to count the number of specific characters in a cell

Another option that is independent of the expected number of leading dots or the length of the text would be a user-defined function.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Dots(s As String) As Long
  Dim i As Long

  For i = 1 To Len(s)
    Select Case Asc(Mid(s, i, 1))
      Case 46: Dots = Dots + 1
      Case 133: Dots = Dots + 3
      Case Else: Exit Function
    End Select
  Next i
End Function

Excel Workbook
	E	F
1	Item Name	Count
2	. Part 1	1
3	Part 2	0
4	.. Part 3	2
5	Part 4	3
6	Part 5	0
7	Part 6	0
8	. Part 7	1
9	.. Part 8	2
10	.. Part 9	5
11	. Bonnet	1
12	.. Cover piece 1 @ 22.5mm	2
13	... Cover piece with attachment 22.5cm x 12.75cm	3
14	...k	9
15	..P2.3	2
16	..	2
Leading Dots

 

[Rick Rothstein]

Re: How to count the number of specific characters in a cell

Here is an array-entered** formula whose only requirement is that there will never be more than 99 leading dots...

=MIN(IF(MID(SUBSTITUTE(A2,CHAR(133),REPT(".",3))&REPT(" ",99),ROW($1:$99),1)<>".",ROW($1:$99)))-1

**Commit this formula using CTRL+SHIFT+ENTER instead of Enter by itself


If you wish to go with a User Defined Function (as suggested by Peter in Message #12 ), here is another one that you can consider...

[table="width: 500"]
[tr]
	[td]Function Dots(S As String) As Long
  Dots = Len(Replace(S, Chr(133), "   ")) - Len(LTrim(Replace(Replace(Replace(S, " ", "x"), ".", " "), Chr(133), "   ")))
End Function[/td]
[/tr]
[/table]

 

[ClippityClop]

assuming there will always be a space after the periods...
=FIND(" ",SUBSTITUTE(A1,CHAR(133),"XXX"))-1