counting how many times a character occurs in a cell

[Jim Nagel]

I seem to have a knack for thinking something will be simple, and then it
turns out not to be.

My spreadsheet is a friend's addresslist for Christmas cards.
Column A is surname
Column B is forename(s)
Column C is address, which will be printed on envelope as
several lines but for now the sheet has character 166 (¦)
(brokenbar); some addresses obviously have more lines than
others
Column D is postcode

In Column E my intention is a formula that will tell me how many times
character 166 appears in Column C of that line.
This is so that I can calculate how many extra newlines I need to
insert after that address when it comes to printing labels -- every label
must have a set number of lines. The result will also reveal cases where
I need to combine some lines in a mouthful-address.

I thought I could use some combination of the COUNT and FIND functions,
but how to cope with my character 166 occurring several times within one
cell?

Wisdom welcome!

Thanks.

--
Jim Nagel www.archivemag.co.uk

[Jim Nagel]

Aha! Found a way to do it, thanks to an Excel tip turned up by a Google
search.

How many times does a given character occur within a cell?

length(d1) - length(substitute(d1,"¦",""))

In the example, d1 is the cell in question, "¦" is the character we're
counting. (The brokenbar, character 166, which I chose arbitarily because
it's narrow but distinctive -- and otherwise unused.)

The formula counts the length of the string,
then replaces each "¦" with a blank,
then counts the length of the remaining string
and outputs the difference.


Source: https://exceljet.net/formula/count-specific-characters-in-a-cell
found in a Google search.
It uses Excel function LEN(), which Fireworkz spells out: length()

--
Jim Nagel www.archivemag.co.uk