Saturday, 2 April 2011

Excel formula

I have a notes application as a replacement for MS Money 2008.  Every month I import all of my account transactions into a notes database, via CSV import and it checks previous entries to categorize each transaction.  This allows me to see where my wife spends all of my money each month!

Occasionally I get lazy and do not download the transactions within the statement period and so the only way to import the  transactions is to copy and paste the HTML table from the statement on the screen.  When I paste this into Excel, it thinks it is text.  The problems start when I try to convert the text into a number, so that it can be imported.  Everything I had tried failed, such as various paste special techniques, convert cell and copy paste from notepad etc...

Today I resolved the issue with a simple formula.  I realized that the text value had a space after it, for example xx.xx_.  I tried to Trim the value, but this did not work either.  In the end I had to use this formula.


I thought I would post this, just so I have a record of the formula for next month, when I miss the deadline for my monthly statement again.  Hopefully, someone else will find this useful.

No comments:

Post a Comment