How to count specific occurrences of a character in a cell while omitting the count of other occurrences of the same character when it appears preceded by other specific characters in google sheets (still keeping the count of the former occurrences in the cell)?
I try to count the occurrences of periods (".") in a cell that isn't preceded by a specific string of characters ("Jr.") (omitting the count of all the occurrences of the string "Jr." while keeping the count of any other period occurrences.
here's the result I'm aiming at:
I've used this formula to try to omit the "Jr." occurrences while still keeping the count of the other period "." occurrences in the cell (and having them summed per cell).
=LEN(D21)-LEN(SUBSTITUTE(SUBSTITUTE(D21,".",""),"<>Jr.",""))
So far, following this post: Count specific characters in a cell - Excel
I've come up with this:
It works to count all the occurrences of the period "." in the cell.
I tried to add the 'is not equal' operator "<>" to the formula but it doesn't work.
=LEN(D21)-LEN(SUBSTITUTE(SUBSTITUTE(D21,".",""),"<>Jr.",""))
What I look for extra is a way to omit the count of the period preceded by "Jr" characters.
So that in the screenshot above it would return as per the following screenshot:
This formula works for me.
=LEN(SUBSTITUTE(B2,"Jr.","x"))-LEN(SUBSTITUTE(SUBSTITUTE(B2,"Jr.","x"),".",""))
Basically we have two steps and below you can look at them separately.
2. Then, using the formula the suggested we calculate the length of the string just created and we subtract the length of the same string where "." are replace with "".