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
$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
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
2013-05-14, but is there any other way?