If Formula
 

I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out. I
need to include that if C26="-All Clubs",54. I need to place the number 54
into the formula field if the data in C26 is equal to "-All Clubs" yet still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out. I
need to include that if C26="-All Clubs",54. I need to place the number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

(I received an error on my initial post, which is why there are 2.) Anyway,
I tried your formula and it comes up with 1, not 54.

Any other thoughts?

TIA
--
Krista


"Bob Phillips" wrote:

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out. I
need to include that if C26="-All Clubs",54. I need to place the number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

What do you have in C26?

Biff

"Krista" wrote in message
...
(I received an error on my initial post, which is why there are 2.)
Anyway,
I tried your formula and it comes up with 1, not 54.

Any other thoughts?

TIA
--
Krista


"Bob Phillips" wrote:

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out.
I
need to include that if C26="-All Clubs",54. I need to place the
number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

This field will be populated from an outside source and will contain a series
of names seperated with ";" or "-All Clubs". I need C27 to then count the
number of ";"+1 for the first type of data or 54 for "-All Clubs".

Hope this helps to clarify.
--
Krista


"Biff" wrote:

What do you have in C26?

Biff

"Krista" wrote in message
...
(I received an error on my initial post, which is why there are 2.)
Anyway,
I tried your formula and it comes up with 1, not 54.

Any other thoughts?

TIA
--
Krista


"Bob Phillips" wrote:

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out.
I
need to include that if C26="-All Clubs",54. I need to place the
number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

That was NG wrap-around, you needed to patch it. Try this version

=IF(C26="","",
IF(C26="-All Clubs",54,
LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
(I received an error on my initial post, which is why there are 2.)
Anyway,
I tried your formula and it comes up with 1, not 54.

Any other thoughts?

TIA
--
Krista


"Bob Phillips" wrote:

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out.
I
need to include that if C26="-All Clubs",54. I need to place the
number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista

If Formula
 

Perfect -- THANK YOU sooo much!

--
Krista


"Bob Phillips" wrote:

That was NG wrap-around, you needed to patch it. Try this version

=IF(C26="","",
IF(C26="-All Clubs",54,
LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
(I received an error on my initial post, which is why there are 2.)
Anyway,
I tried your formula and it comes up with 1, not 54.

Any other thoughts?

TIA
--
Krista


"Bob Phillips" wrote:

=IF(C26="","",IF(C26="-All
Clubs",54,LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Krista" wrote in message
...
I currently have a formula:

=IF(C26="","",LEN(C26)-LEN(SUBSTITUTE(C26,";",""))+1)

But I need to add another function to it somehow and I'm striking out.
I
need to include that if C26="-All Clubs",54. I need to place the
number
54
into the formula field if the data in C26 is equal to "-All Clubs" yet
still
have it count the ";" for all other data that will be input.

Any suggestions? -- THANKS!
--
Krista