software

I've got another software program that will be filling a cell in Excel based
on a multi-select drop-down list. The Excel cell could read

Algonquin;Bloomingdale;Burr Ridge

I'm trying to count the occurrences of ; in that cell then add 1 to get a
total of the number of drop-down selections made from the other program.

The value that I'm looking for here in this example is 3. I've tried
everything that I know to no avail.

Thanks much in advance!!!
--
Krista

Try this:

For a value in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1

That formula counts the semicolons in cell A1 and adds 1 to the total

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;Kristaquot; wrote:

gt; I've got another software program that will be filling a cell in Excel based
gt; on a multi-select drop-down list. The Excel cell could read
gt;
gt; Algonquin;Bloomingdale;Burr Ridge
gt;
gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; total of the number of drop-down selections made from the other program.
gt;
gt; The value that I'm looking for here in this example is 3. I've tried
gt; everything that I know to no avail.
gt;
gt; Thanks much in advance!!!
gt; --
gt; Krista

=LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1

what if there are no semicolons, do you want 0 1 or 0 only, for the former
the above formula works, for the latter

=LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) ISNUMBER(FIND(quot;;quot;,A1))

which will work for the former as well--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Kristaquot; gt; wrote in message
...
gt; I've got another software program that will be filling a cell in Excel
gt; based
gt; on a multi-select drop-down list. The Excel cell could read
gt;
gt; Algonquin;Bloomingdale;Burr Ridge
gt;
gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; total of the number of drop-down selections made from the other program.
gt;
gt; The value that I'm looking for here in this example is 3. I've tried
gt; everything that I know to no avail.
gt;
gt; Thanks much in advance!!!
gt; --
gt; Krista
Perfect -- Thanks much!
--
Kristaquot;Peo Sjoblomquot; wrote:

gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1
gt;
gt; what if there are no semicolons, do you want 0 1 or 0 only, for the former
gt; the above formula works, for the latter
gt;
gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) ISNUMBER(FIND(quot;;quot;,A1))
gt;
gt; which will work for the former as well
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Kristaquot; gt; wrote in message
gt; ...
gt; gt; I've got another software program that will be filling a cell in Excel
gt; gt; based
gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt;
gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt;
gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; total of the number of drop-down selections made from the other program.
gt; gt;
gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; everything that I know to no avail.
gt; gt;
gt; gt; Thanks much in advance!!!
gt; gt; --
gt; gt; Krista
gt;
gt;
gt;

One more variation:

=LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) (A1lt;gt;quot;quot;)

If you have
Schaumburg
in a cell by itself.
Krista wrote:
gt;
gt; I've got another software program that will be filling a cell in Excel based
gt; on a multi-select drop-down list. The Excel cell could read
gt;
gt; Algonquin;Bloomingdale;Burr Ridge
gt;
gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; total of the number of drop-down selections made from the other program.
gt;
gt; The value that I'm looking for here in this example is 3. I've tried
gt; everything that I know to no avail.
gt;
gt; Thanks much in advance!!!
gt; --
gt; Krista

--

Dave Peterson

I guess there is one more thing ... I do still want it to 1 if there is one
item in A1, which would not have any ; ... however, I want the value to be
blank if A1 is also blank ... if possible.

Thanks!

--
Kristaquot;Kristaquot; wrote:

gt; Perfect -- Thanks much!
gt; --
gt; Krista
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1
gt; gt;
gt; gt; what if there are no semicolons, do you want 0 1 or 0 only, for the former
gt; gt; the above formula works, for the latter
gt; gt;
gt; gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) ISNUMBER(FIND(quot;;quot;,A1))
gt; gt;
gt; gt; which will work for the former as well
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; nwexcelsolutions.com
gt; gt;
gt; gt;
gt; gt; quot;Kristaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I've got another software program that will be filling a cell in Excel
gt; gt; gt; based
gt; gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt; gt;
gt; gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt; gt;
gt; gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; gt; total of the number of drop-down selections made from the other program.
gt; gt; gt;
gt; gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; gt; everything that I know to no avail.
gt; gt; gt;
gt; gt; gt; Thanks much in advance!!!
gt; gt; gt; --
gt; gt; gt; Krista
gt; gt;
gt; gt;
gt; gt;

No, that doesn't help because if I have Schaumburg in the field I do still
want it to 1. I'm now looking for what it should be if the field is blank
so it returns blank.

Thanks.
--
Kristaquot;Dave Petersonquot; wrote:

gt; One more variation:
gt;
gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) (A1lt;gt;quot;quot;)
gt;
gt; If you have
gt; Schaumburg
gt; in a cell by itself.
gt;
gt;
gt;
gt; Krista wrote:
gt; gt;
gt; gt; I've got another software program that will be filling a cell in Excel based
gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt;
gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt;
gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; total of the number of drop-down selections made from the other program.
gt; gt;
gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; everything that I know to no avail.
gt; gt;
gt; gt; Thanks much in advance!!!
gt; gt; --
gt; gt; Krista
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Per your second request...

Try this:
B1: =IF(A1=quot;quot;,quot;quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1)

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; For a value in A1
gt; B1: =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1
gt;
gt; That formula counts the semicolons in cell A1 and adds 1 to the total
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Kristaquot; wrote:
gt;
gt; gt; I've got another software program that will be filling a cell in Excel based
gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt;
gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt;
gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; total of the number of drop-down selections made from the other program.
gt; gt;
gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; everything that I know to no avail.
gt; gt;
gt; gt; Thanks much in advance!!!
gt; gt; --
gt; gt; Krista

Yes, thank you!
--
Kristaquot;Ron Coderrequot; wrote:

gt; Per your second request...
gt;
gt; Try this:
gt; B1: =IF(A1=quot;quot;,quot;quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1)
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; For a value in A1
gt; gt; B1: =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) 1
gt; gt;
gt; gt; That formula counts the semicolons in cell A1 and adds 1 to the total
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;Kristaquot; wrote:
gt; gt;
gt; gt; gt; I've got another software program that will be filling a cell in Excel based
gt; gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt; gt;
gt; gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt; gt;
gt; gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; gt; total of the number of drop-down selections made from the other program.
gt; gt; gt;
gt; gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; gt; everything that I know to no avail.
gt; gt; gt;
gt; gt; gt; Thanks much in advance!!!
gt; gt; gt; --
gt; gt; gt; Krista

This should return 0 if A1 is empty. But it'll return 1 if you only have a
single city name in A1.

Krista wrote:
gt;
gt; No, that doesn't help because if I have Schaumburg in the field I do still
gt; want it to 1. I'm now looking for what it should be if the field is blank
gt; so it returns blank.
gt;
gt; Thanks.
gt; --
gt; Krista
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; One more variation:
gt; gt;
gt; gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot;;quot;,quot;quot;)) (A1lt;gt;quot;quot;)
gt; gt;
gt; gt; If you have
gt; gt; Schaumburg
gt; gt; in a cell by itself.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Krista wrote:
gt; gt; gt;
gt; gt; gt; I've got another software program that will be filling a cell in Excel based
gt; gt; gt; on a multi-select drop-down list. The Excel cell could read
gt; gt; gt;
gt; gt; gt; Algonquin;Bloomingdale;Burr Ridge
gt; gt; gt;
gt; gt; gt; I'm trying to count the occurrences of ; in that cell then add 1 to get a
gt; gt; gt; total of the number of drop-down selections made from the other program.
gt; gt; gt;
gt; gt; gt; The value that I'm looking for here in this example is 3. I've tried
gt; gt; gt; everything that I know to no avail.
gt; gt; gt;
gt; gt; gt; Thanks much in advance!!!
gt; gt; gt; --
gt; gt; gt; Krista
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson