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

 

[MrKowz]

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

You can nest multiple substitutes together. Try:

Excel 2013/2016
	A	B	C
1	Column A - Data	Count (Desired)	Count (Formula)
2	5+4+5+3.0-6	5	5
3	8-3+6-3-1	5	5
4	9+4+2-2-2-1	6	6
Sheet1

Cell Formulas
Range	Formula
C2	=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"+",""),"-",""))+1

 

[JayReddy]

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

Hi MrKowz,

Thanks a lot for your help - this works perfectly

I have an additional query for a similar formula that I hope you can also help with please ..... is it possible to count the number of individual entries in a cell when calculating a sum?

For example: +2+2+4-1 = a total of 7 but is 4 separate entries.

Using a COUNT formula I can only seem to get Excel to count these entries as 1 singular entry.

I am using a spreadsheet to calculate both the number of individual adjustments made in an accounting period and their total value. Each +/- in an individual cell represents 1 adjustmentand I need to find a formula to both calculate and report this accurately.

Best Regards,
Jay

 

[MrKowz]

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

I'm not sure I follow - can you please give me some extra examples, the desired output, and your thought process in how you arrived to the output?

 

[bristolrob]

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

Good morning
I am having an issue with counting characters that i hope someone might be able to resolve for me
I am trying to count the number of times a full stop appears before a part name i.e

[TABLE="width: 369"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Item Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]. Part 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD].. Part 3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]… Part 4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]. Part 7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD].. Part 8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]….. Part 9[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]

The issue i am having using all the available formulas is that none of them seem to be able to deal with more than 2 full stops, and i am getting the following result
Using the formula

=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))

[TABLE="width: 369"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Item Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]. Part 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD].. Part 3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]… Part 4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Part 6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]. Part 7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD].. Part 8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]….. Part 9[/TD]
[TD="align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]


Any help on this is gratefully received

 

[Fluff]

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

Your problem is that ... is not 3 periods but an ellipses.
There's probably a cleaner way but try
=LEN(A5)-LEN(SUBSTITUTE(A5,".",""))+((LEN(A5)-LEN(SUBSTITUTE(A5,CHAR(133),"")))*3)

 

[bristolrob]

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

Fluff

Thanks very much that works a treat

 

[Fluff]

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

You're welcome

 

[bristolrob]

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

Hi guys. Although the above solution worked perfectly i have an amendment to it and am wondering if you might have a solution
If i have the following item where there are more than 1 full stop within the part name, would it be possible to just pick up the leading full stops and not the ones within the part name ? (examples below)

. Bonnet
.. Cover piece 1 @ 22.5mm
... Cover piece with attachment 22.5cm x 12.75cm

Apologies for changing the requirement and thanks for the initial solution

 

[Fluff]

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

How about
=7-LEN(SUBSTITUTE(LEFT(E2,7),".",""))+((7-LEN(SUBSTITUTE(LEFT(E2,7),CHAR(133),"")))*3)
change the 7s to a number that is less then the shortest string length, but more than the number of . you are likely to get.