Parsing a string as an array in Excel 2010 (without VBscript)

You can actually do this with no VBA involved. The method is kind of ridiculous, though. But, just for fun...

In cell B4 I put one of the strings you want to "parse":

B4:  pcmsh15(232);pcmsh16(232);pcmsh17(136);

Then I put the following formulas in the cells indicated:

B6:  =SUBSTITUTE(B4,"pcmsh", """dummy")
B8:  =SUBSTITUTE(B6,"(",""";")
B10: =SUBSTITUTE(B8,")","")
B12: =MID(B10, 1, LEN(B10)-1)
B14: ="{"&B12&"}"

That transforms the starting text into a text representation of a legal Excel array:

B14: {"dummy15";232;"dummy16";232;"dummy17";136}

You can make Excel evaluate that string and return an actual array with the following trick. Create a named formula that we'll call eval_left. Make sure cell C14 is selected when you create the named formula:

=EVALUATE(B14)

Note that the formula uses a relative reference, so if you use that name from a cell other than C14, you'll see that the B14 has changed to whatever cell is just to the left.

This uses the old Excel 4 macro function EVALUATE, which takes a string and returns the value that that string represents. Now we can put a final formula in cell C14:

C14: =SUM(eval_left)

Since SUM ignores non-numeric strings, you get the answer you want, with no VBA. (You do still get the macro warning though, because of the use of EVALUATE.)

Like I said, I consider this a just-for-fun curiousity. You're way better off using VBA for stuff like this.

EDIT:

In the unlikely event you wanted to actually do something like this, you'd run into the length limit of what EVALUATE can handle. You'd have to chop up your longer strings into less than 65 values, not use "dummy", etc., but it would still be doable.