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

https://exceljet.net/formulas/text-split-to-array

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.