You can extract those columns from that secured tab, to another tab and then do the text to columns in that new tab....

However, I don't see why the wildcard won't work.

If you have the word "Georgia" in say A2 of your summary tab, why couldn't you use =SUMIF('Other tab'!A:A,"*"&A2&"*",'Other tab'!B:B)

This will look at A2 for the word to search for in column A of the Other tab, and sum column B.

You can copy formula down, for example, to get state that is in A3, A4, etc...