How to Split and Sum Text numbers using FILTERXML

If you do not have Office365, the easiest option you have for splitting text is FILTERXML

How do you split and sum below multiple sales?

The easiest way to solve this is by using the FILTERXML function

FILTERXML function returns specific data from XML text using the specified XPath expression

=FILTERXML(xml, xpath)

Step 1: Add XML markup to the text

This is done by substituting the delimiter in the text with some XML closing and opening tags

=SUBSTITUTE(C3, " ," , "</a><a>") ►► 6399</a><a> 3964</a><a> 7891</a><a> 3055</a><a> 7268					

Step 2: Complete the XML by appending more opening and closing tags

="<b><a>"&SUBSTITUTE(C3, " , ","</a><a>")&"</a></b>"

results to below

<b>
<a>6399</a>
<a>3964</a>
<a>7891</a>
<a>3055</a>
<a>7268</a>
</b>

Step 3: Use FILTERXML to select every element

=FILTERXML("<b><a>"&SUBSTITUTE(C3, " , ","</a><a>")&"</a></b>","//a") ► {6399;3964;7891;3055;7268}

Step 4: Use the SUM function to add up the numbers

=SUM(FILTERXML("<b><a>"&SUBSTITUTE(C3, " , ","</a><a>")&"</a></b>","//a"))

See step by step Video

DOWNLOAD WORKSHEET

Learn More

Extracting the Last Word In Excel