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,";",""))+1

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

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

XL2002, WinXP


"Krista" wrote:

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

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+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,";",""))+ISNUMBER(FIND(";",A1))

which will work for the former as well


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Krista" wrote in message
...
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

Perfect -- Thanks much!
--
Krista


"Peo Sjoblom" wrote:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+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,";",""))+ISNUMBER(FIND(";",A1))

which will work for the former as well


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Krista" wrote in message
...
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

One more variation:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+(A1<"")

If you have
Schaumburg
in a cell by itself.



Krista wrote:

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

--

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!

--
Krista


"Krista" wrote:

Perfect -- Thanks much!
--
Krista


"Peo Sjoblom" wrote:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+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,";",""))+ISNUMBER(FIND(";",A1))

which will work for the former as well


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Krista" wrote in message
...
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

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.
--
Krista


"Dave Peterson" wrote:

One more variation:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+(A1<"")

If you have
Schaumburg
in a cell by itself.



Krista wrote:

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

--

Dave Peterson

Per your second request...

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

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

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

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

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

XL2002, WinXP


"Krista" wrote:

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

Yes, thank you!
--
Krista


"Ron Coderre" wrote:

Per your second request...

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

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

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

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

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

XL2002, WinXP


"Krista" wrote:

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

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:

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.
--
Krista

"Dave Peterson" wrote:

One more variation:

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+(A1<"")

If you have
Schaumburg
in a cell by itself.



Krista wrote:

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

--

Dave Peterson


--

Dave Peterson