Need to be able to count data from two columns that contain multiple entries in the cell

[Eagle3733]

Hello,

I have an excel sheet that is exported from a SharePoint site that lumps all data into two main fields. So the 1st column is a company name, the 2nd is states where they have branches (separated by a semi-colon # within the cell) and the 3rd is their areas of expertise (once again separated by a semi-colon#). I know how to do a count to say how many have branches in each state and how many list a certain area of expertise. Is there a way to calculate how many have state and expertise? So if I want to know how many have branches in TX and Automobile as an area of expertise? It would need to search for TX in the cell and the same for automobile. I tried to separate the fields based on the spaces(semi-colons) and then pivot table, with no success. Any one with a good idea??? I know that means if I have 50 states and 25 areas of expertise, that I am looking for 1250 results... Thanks for any help!

Example:
[TABLE="width: 1500"]
<tbody>[TR]
[TD][TABLE="width: 508"]
<tbody>[TR]
[TD="class: xl68, width: 508"]#Admin/Clerical;#Accounting/Finance;#Human Resources;#Clinical;#Light Industrial;#Engineering;#Technical;#Facilities;#Skilled Trades;#Marketing;#Communications;#Media;#Scientific
Healthcare;#Telecom;#Creative
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 304"]
<tbody>[TR]
[TD="class: xl68, width: 304"]#AL;#AK;#AR;#AS;#AZ;#CA;#CO;#CT;#DC;#DE;#FL;#GA;#HI;#IA;#ID;#IL;#IN;#KS;#KY;#LA;#MA;#MD;#ME;#MI;#MN;#MO;#MS;#MT;#NC;#ND;#NE;#NH;#NJ;#NM;#NV;#NY;#OH;#OK;#OR;#PA;#RI;#SC;#SD;#TN;#TX;#UT;#VA;#VT;#WA;#WI;#WV;#BC;#MB;#NB;#NL;#NT;#NS;#NU;#ON;#PE;#QC;#SK;#YT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]