How to Exclude text in string in a single cell

**leopoldc** · 10-27-2013, 04:36 AM

Hi Guys

I have been using to count the strings in between commas in ONE cell

=IF(A2<>"",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1,"")

However now i'd like to exclude both the text : "Supervisor" and "Preliminary" string from my cell. ie

One Cell has a list of trades: Joinery & Cabinets, Plumbing, Plaster, Carpentry and Joinery, Painter and Decorator, Electrician, No Class Name, Internal Tiling, Preliminary, Concreter, Supervisor, Asbestos, Floor Sanding, Demolition , Low Rise Builder, Garage Doors

Using the IF statement I get a count: 16

I'd like for the IF statement to be able to give a count of 14; by not counting "Supervisor" and "Preliminary" text in the string, thanks guys

Thanks guys for looking at this for me.

**Pepe Le Mokko** · 10-27-2013, 04:57 AM

Perhaps

Please Login or Register  to view this content.

**leopoldc** · 10-28-2013, 06:21 AM

Thanks for trying it out Pepe. Unfortunately didn't work.....

I've uploaded a sample file. I am trying to replicate the light blue "count of trade class" field, without using Macros VB

Thanks guysss....