Split a string (cell) in Excel without VBA (e.g. for array formula)

Question

I have data in an Excel spreadsheet with delimited strings. For simplicity, think of something like "4#8#10", with an arbitrary number of pieces.

Is there any way to split this into an array for use with an array formula? For example, something like SUM(SplitStr(A1,"#")). (This does not work - it just returns "4".)

Is there a good way of approaching this? My ideal solution would end up with an array, as if the user entered {4,8,10} for use in array formulas.

The application of my sheet does not allow for delimiter-based text to columns and summing that. I would prefer to not use VBA if possible as I will not be the primary consumer of the sheet.

Thanks, Terry

Answer 1

Use XML functionality:

={SUM(FILTERXML("<t><s>" & SUBSTITUTE(A1, "#", "</s><s>") & "</s></t>", "//s"))}

Update ^{April 2022}: Use new TEXTSPLIT function.