3

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • That works for dates, but what about the currency values?
    – Dai
    Commented Aug 19, 2015 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
    Commented Aug 20, 2015 at 10:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .