Count ; in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!!
 
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
 
=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
 
Perfect -- Thanks much!
--
Krista


Peo Sjoblom said:
=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
Click to expand...
 
One more variation:

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

If you have
Schaumburg
in a cell by itself.
 
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!
 
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.
 
Per your second request...

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

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

XL2002, WinXP
 
Yes, thank you!
--
Krista


Ron Coderre said:
Per your second request...

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

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

XL2002, WinXP
Click to expand...
 
This should return 0 if A1 is empty. But it'll return 1 if you only have a
single city name in A1.
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.
Click to expand...
 
Back
Top