I frequently copy and paste HTML tables into Excel to do quick aggregate operations (e.g. sums, averages, etc).

In this example, PayPal, their HTML table contains text values like

    A          | B       | C
1 | Date       | Type    | Net
2 | 5/14/2013  | Payment | $4.70 USD

I want to get the actual Date and Currency values from this table into a format that Excel understands (note that my computer is set to "English (United Kingdom)" and I custom-set my Windows Short Date format to yyyy-MM-dd) and my default Excel currency is £ GBP.

I create a new column in Excel, D, and set the cells to =VALUE( A2 ) and a Currency column E set to =VALUE( C2 ).

However for both columns, D and E, Excel gives me the #VALUE! error (because it won't parse MM/dd/yyyy as a date as it expects yyyy-MM-dd, nor $x.xx USD as it expects £x.xx - so I need to get it to ignore both the initial currency symbol and the trailing 3-character name USD)

I don't want to have to write an ugly string-parsing function in VBA (or worse, Excel formula syntax) to convert "$4.70 USD" into 4.7 or "5/14/2013" into 2013-05-14, but is there any other way?


Use the Text Import Wizard. You might have this option in the paste button that appears when you paste in your text. If not, it's in the Data tab of the ribbon. Click Text to Columns. When you get to step 3, import column A as Date: MDY. Then you shouldn't have to use the VALUE function for your dates.

  • That works for dates, but what about the currency values? – Dai Aug 19 '15 at 21:56
  • In that case, the formula =VALUE(LEFT(C2,FIND(" ",C2))) should chop the trailing "USD" off and convert the text to a number. – benshepherd Aug 20 '15 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.