A Working IF Statement that I cannot adapt for Conditional Formatting

[netrixuser]

Hello all,

My goal is to highlight a row of cells where one cell in that row contains more than 4 commas.

I started with this IF just to prove the logic and it returns TRUE

=IF(E37="",0,LEN(E37)-LEN(SUBSTITUTE(E37,",","")))>4

My Conditional Format Formula currently looks like this - but it does nothing. Even though cell E37 contains 12 commas

="IF(E37="""",0,LEN(E37)-LEN(SUBSTITUTE(E37,"","","""")))>4"

Am I doing something obviously wrong - or is there an easier way to achieve my goal of highlighting a row if one cell in that row contains more than 4 commas ?

Any help very much appreciated.

Regards

Netrix

 

[RoryA]

There should not be quotes after the = sign or at the end of the formula. That makes it a text string.

 

[awoohaw]

What version of excel are you using?
Are you counting commas in text, formulas, or both?

 

[SanjayGMusafir]

Is this what you are trying to achieve -

Book1
	A	B	C
1	Try, Some, thing, different, to, surprise	5	Test
2	Try,Some,thing,different,to,surprise	5	Test
3	Try, Some, thing, different, to, surprise	5	Test
4		0	Test
5	Try, Some, thing	2	Test
6	Try, Some, thing, different, to, surprise		Test
7	Try, Some, thing, different,	4	Test
Sheet1

Cell Formulas
Range	Formula
B1:B5,B7	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C7	Expression	=AND($A1<>"",LEN($A1)-LEN(SUBSTITUTE($A1,",",""))>4)	text	NO

 

[netrixuser]

There should not be quotes after the = sign or at the end of the formula. That makes it a text string.

Hi RoryA - Thank you for your reply,
I changed the Condition Format formula to =IF(E37="""",0,LEN(E37)-LEN(SUBSTITUTE(E37,"","","""")))>4 but it made no difference.

 

[netrixuser]

What version of excel are you using?
Are you counting commas in text, formulas, or both?

Hi awoohaw,
Its Office 365 and just counting commas in text

 

[Fluff]

Its Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For conditional formatting you can use

=LEN(E37)-LEN(SUBSTITUTE(E37,",",""))>4

 

[netrixuser]

Is this what you are trying to achieve -

Book1
	A	B	C
1	Try, Some, thing, different, to, surprise	5	Test
2	Try,Some,thing,different,to,surprise	5	Test
3	Try, Some, thing, different, to, surprise	5	Test
4		0	Test
5	Try, Some, thing	2	Test
6	Try, Some, thing, different, to, surprise		Test
7	Try, Some, thing, different,	4	Test
Sheet1

Cell Formulas
Range	Formula
B1:B5,B7	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C7	Expression	=AND($A1<>"",LEN($A1)-LEN(SUBSTITUTE($A1,",",""))>4)	text	NO

Thank you for this, SanjayGMusafir,

That was what I was looking for, although I could not get it work as I wanted.
I moved on slightly in my "wants" to try and write the Conditional Format written into some VBA code, I that has been successful - I'm sure I must have had a typo when trying to use CF in Excel !!

Thanks again.

 

[netrixuser]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For conditional formatting you can use

=LEN(E37)-LEN(SUBSTITUTE(E37,",",""))>4

Thanks for this Fluff,

I have updated my profile info.
As per my reply above, my "wants" changed slightly but with yours and SanjayGMusafir's help I am sorted..... for now !!

Kind regards

 

[Fluff]

Glad you sorted it & thanks for letting us know.