Excel - Convert dates and currency as text to Excel dates and numbers respectively

Question

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?

Solution

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.

Comments

[Khalid]

That works for dates, but what about the currency values?

[benshepherd]

In that case, the formula =VALUE(LEFT(C2,FIND(" ",C2))) should chop the trailing "USD" off and convert the text to a number.