Trying to extract number from XML

[sachin86]

Hello,

Thank you so much for your time and help! I am trying to extract tax data from a large XML that contains several things. Here is an example of a piece of data:

<Order>
<OrderID>105-8920931-7716261</OrderID>
<PurchaseDate>2011-05-01T04:39:41+00:00</PurchaseDate>
<LastUpdatedDate>2011-05-01T11:38:02+00:00</LastUpdatedDate>
<OrderStatus>Shipped</OrderStatus>
<SalesChannel>merchant</SalesChannel>
<FulfillmentData>
<FulfillmentChannel>Merchant</FulfillmentChannel>
<ShipServiceLevel>Standard</ShipServiceLevel>
<Address>
<City>SHERMAN</City>
<State>TX</State>
<PostalCode>75090-3488</PostalCode>
<Country>US</Country>
</Address>
</FulfillmentData>
<OrderItem>
<ASIN>B0022R74BC</ASIN>
<SKU>MN-UPDC-Q1H3</SKU>
<ItemStatus>Shipped</ItemStatus>
<ProductName>Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades</ProductName>
<Quantity>2</Quantity>
<ItemPrice>
<Component>
<Type>Principal</Type>
<Amount currency="USD">140.0</Amount>
</Component>
<Component>
<Type>Tax</Type>
<Amount currency="USD">8.76</Amount>
</Component>
<Component>
<Type>Shipping</Type>
<Amount currency="USD">23.99</Amount>
</Component>
<Component>
<Type>ShippingTax</Type>
<Amount currency="USD">1.5</Amount>
</Component>
</ItemPrice>
</OrderItem>
</Order>


I have a list of thousands of these in one single column (one after the other). What I need to extract is the "Tax" amount collected. In this case, Tax is represented by:

<Component>
<Type>Tax</Type>
<Amount currency="USD">8.76</Amount>
</Component>
<Component>



What I want is an easy way to extract the 8.76 and perhaps put it in a separate column or some way in which I can add all of the taxes collected in an XML file that contains thousands of these individual transactions.

Your help would be much appreciated! Thanks so much for a great forum!!

 

[hiker95]

sachin86,


If your raw data looks like this:

Excel Workbook
	A
1	105-8920931-7716261
2	2011-05-01T04:39:41+00:00
3	2011-05-01T11:38:02+00:00
4	Shipped
5	merchant
6
7	Merchant
8	Standard
9
10
11	SHERMAN
12	TX
13	75090-3488
14	US
15
16
17
18
19	B0022R74BC
20	MN-UPDC-Q1H3
21	Shipped
22	Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades
23	2
24
25
26	Principal
27	140
28
29
30	Tax
31	8.76
32
33
34	Shipping
35	23.99
36
37
38	ShippingTax
39	1.5
40
41
4282070483

Or, if your raw data looks like this:

Excel Workbook
	A
1	*105-8920931-7716261
2	*2011-05-01T04:39:41+00:00
3	*2011-05-01T11:38:02+00:00
4	*Shipped
5	*Merchant
6	*
7	* *Merchant
8	* *Standard
9	* *
10	* * *SHERMAN
11	* * *TX
12	* * *75090-3488
13	* * *US
14	* *
15	*
16	*
17	* *B0022R74BC
18	* *MN-UPDC-Q1H3
19	* *Shipped
20	* *Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades
21	* *2
22	* *
23	* * *
24	* * * *Principal
25	* * * *140.0
26	* * *
27	* * *
28	* * * *Tax
29	* * * *8.76
30	* * *
31	* * *
32	* * * *Shipping
33	* * * *23.99
34	* * *
35	* * *
36	* * * *ShippingTax
37	* * * *1.5
38	* * *
39	* *
40	*
41
4282070483

The outcome will be the same:

Excel Workbook
	C	D
1	Tax	ShippingTax
2	8.76	1.5
3
4282070483

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetTaxV2()
' hiker95, 07/19/2011
' http://www.mrexcel.com/forum/showthread.php?p=2792897
Dim c As Range, NR As Long, Sp
Application.ScreenUpdating = False
Columns("C:D").ClearContents
Range("C1:D1") = [{"Tax","ShippingTax"}]
NR = 1
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c = "" Then
    'do nothing
  ElseIf Right(c, 3) = "Tax" And Right(c, 11) <> "ShippingTax" Then
    NR = NR + 1
    Range("C" & NR).Value = Range("A" & c.Row + 1).Value
    If InStr(Range("C" & NR), "*") > 0 Then
      Sp = Split(Range("C" & NR), "*")
      Range("C" & NR).Value = Sp(UBound(Sp))
    End If
  ElseIf Right(c, 11) = "ShippingTax" Then
    Range("D" & NR).Value = Range("A" & c.Row + 1).Value
    If InStr(Range("D" & NR), "*") > 0 Then
      Sp = Split(Range("D" & NR), "*")
      Range("D" & NR).Value = Sp(UBound(Sp))
    End If
  End If
Next c
Columns("C:D").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the GetTaxV2 macro.

 

[hiker95]

sachin86,

Ooops, posted it twice by mistake.