Sum up numbers in a string of text in a single cell

[sjn6900]

Hi,

I have some output that is being presented to us in excel (relates to order details - each order in a new cell).

In each cell, i have a string of information that includes things like the following:

<ItemNumber>1</ItemNumber> <Quantity>1</Quantity> <NetWeight>1.412</NetWeight> <GrossWeight>1.412</GrossWeight> <ItemNumber>2</ItemNumber> <Quantity>1</Quantity> <UnitPrice>0</UnitPrice> <NetWeight>1.41</NetWeight> <GrossWeight>1.41</GrossWeight> <ItemNumber>3</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.002</NetWeight> <GrossWeight>0.002</GrossWeight> <ItemNumber>4</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.06</NetWeight> <GrossWeight>0.06</GrossWeight> <ItemNumber>5</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.268</NetWeight> <GrossWeight>0.268

I need a way to be able to search through the text in each cell and sum up all of the numbers shown in the Gross Weight sections. The number of items whose gross weights need to be summed up will be different in each cell. The numbers that need to be summed will always have <GrossWeight> before and </GrossWeight> after.

I can't figure a way to use the usual text functions, so assume some VBA would be the best way to deal with this (way beyond my knowledge i'm afriad!)
Any help would be much appreciated.

thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

The numbers that need to be summed will always have <GrossWeight> before and </GrossWeight> after.

So the total would not include that last 0.268 in your sample?
.. or is the sample not complete?

 

[sjn6900]

it would - i've not completed the sample properly. sorry about that.

 

[Peter_SSs]

See if this works for you.

gifariz.xlsm
	A	B
1	<ItemNumber>1</ItemNumber> <Quantity>1</Quantity> <NetWeight>1.412</NetWeight> <GrossWeight>1.412</GrossWeight> <ItemNumber>2</ItemNumber> <Quantity>1</Quantity> <UnitPrice>0</UnitPrice> <NetWeight>1.41</NetWeight> <GrossWeight>1.41</GrossWeight> <ItemNumber>3</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.002</NetWeight> <GrossWeight>0.002</GrossWeight> <ItemNumber>4</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.06</NetWeight> <GrossWeight>0.06</GrossWeight> <ItemNumber>5</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.268</NetWeight> <GrossWeight>0.268</GrossWeight>	3.152
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"x",">",""),"<",""),"/",""),"GrossWeight","</c><c>")&"</c></p>","//c"))

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1
2	<ItemNumber>1</ItemNumber> <Quantity>1</Quantity> <NetWeight>1.412</NetWeight> <GrossWeight>1.412</GrossWeight> <ItemNumber>2</ItemNumber> <Quantity>1</Quantity> <UnitPrice>0</UnitPrice> <NetWeight>1.41</NetWeight> <GrossWeight>1.41</GrossWeight> <ItemNumber>3</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.002</NetWeight> <GrossWeight>0.002</GrossWeight> <ItemNumber>4</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.06</NetWeight> <GrossWeight>0.06</GrossWeight> <ItemNumber>5</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.268</NetWeight> <GrossWeight>0.268 </GrossWeight>	3.152
Report

Cell Formulas
Range	Formula
B2	B2	=SUM(FILTERXML("<k>"&A2&"</k>","//GrossWeight"))

 

[Peter_SSs]

Another option

.. just a bit shorter than mine.

Another option if the TEXTSPLIT function is available in your 365 version.

gifariz.xlsm
	A	B
1	<ItemNumber>1</ItemNumber> <Quantity>1</Quantity> <NetWeight>1.412</NetWeight> <GrossWeight>1.412</GrossWeight> <ItemNumber>2</ItemNumber> <Quantity>1</Quantity> <UnitPrice>0</UnitPrice> <NetWeight>1.41</NetWeight> <GrossWeight>1.41</GrossWeight> <ItemNumber>3</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.002</NetWeight> <GrossWeight>0.002</GrossWeight> <ItemNumber>4</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.06</NetWeight> <GrossWeight>0.06</GrossWeight> <ItemNumber>5</ItemNumber> <Quantity>1</Quantity> <NetWeight>0.268</NetWeight> <GrossWeight>0.268</GrossWeight>	3.152
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM(IFERROR(TEXTSPLIT(SUBSTITUTE(A1,"/",""),"<GrossWeight>")+0,0))

 

[sjn6900]

Hi,

I don't have the textsplit function yet in my O365 release.

the =SUM(FILTERXML("<k>"&A2&"</k>","//GrossWeight")) was perfect.

thanks so much for the quick response.

 

[Fluff]

Glad we could help & thanks for the feedback.