Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: Using Excel with downloaded bank statements. By Simon Hurst

by
14th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

In a recent article looking at the accounting software options available to smaller organisations, David Carter suggested using bank statements as the basis of an electronic cash book. As David pointed out, with the banks now providing access to account details online, it is possible to download statement information in electronic form and use it within a spreadsheet such as Excel.

However, whilst some banks provide information that Excel can interpret without problems, it is clear from some recent queries to The Knowledge Base's eSupport service that this is not always the case. In two recent cases the issue was the formatting of monetary values. Here we will look at some Excel functions that may help if you are having problems using your bank's downloaded information.

There are three Excel functions that should help convert values entered as text into values that Excel can work with as numbers:

VALUE() will convert a straightforward number entered as text into a value

TRIM() will remove all spaces from a text string except single spaces between words

CLEAN() will remove non-printable characters from a text string

VALUE() will often work, even where the original entry includes spaces and currency symbols. However, other characters will stop VALUE() being able to interpret the cell contents and result in a '#VALUE!' Error. It is worth trying to use VALUE() with CLEAN() as follows:

=VALUE(CLEAN(A3))

This will not always work, depending on the extraneous characters that are included. If VALUE() does not work, click on the cell containing the text value and in the formula bar see if there are blank characters at the beginning or end of the text string:

Here we have clicked beyond the right hand end of the visible text, the insertion point is positioned a little to the right of the '9' showing that there are extra characters to the right of the number itself. You can use the left arrow key on the keyboard to move across the extra characters to see how many there are ' in this case two.
We could now use the LEFT() and LEN() function to remove these closing characters ' hoping, of course, that the bank download has uniformly added two characters to every entry:

=LEFT(A1,LEN(A1)-2)

Here we see LEFT() used to take the contents of cell A1 and return all but the two rightmost characters. This is achieved by counting the number of characters to be returned as the length of the text in cell A1 - LEN(A1) ' minus 2, being the two characters we need to exclude.

We can combine this with VALUE() to convert our text to a number:

=VALUE(LEFT(A1,LEN(A1)-2))

Here we can see our original entry in cell A1, the use of our LEFT() and LEN() functions in B1, and our complete formula using VALUE() in C1. We have included SUM() formulae in row 3 to show that it is only the use of the VALUE() function that results in a useable number:

If you are curious as to which spurious characters your bank is inflicting on you, you can use the CODE() function to find out. CODE() returns the ANSI character number of the first character in the cell or text string it refers to. So in our case we could use:

=CODE(RIGHT(A1,1))

This would return the ANSI value of the rightmost character in A1. As an example, '0' is ANSI character number 48, '1' is 49 and so on. So CODE("15.90") would return 49.

Tags:

Replies (2)

Please login or register to join the discussion.

avatar
By carpentermr
14th Aug 2006 11:14

Substitute to get rid of spaces
If you want to remove all spaces from a cell, instead of having to work out where the space might be and use LEFT, RIGHT or MID to remove said spaces, you can just use the "SUBSTITUTE" command.

The syntax is:-

=SUBSTITUTE(A6," ","") (where A6 is a cell ref, the first set of quotation marks include the character to be found and then the second set of quotation marks include what it is to be replaced with)

You can obviously use this quite extensively to remove any form of text characters.

Thanks (0)
avatar
By reptile
17th Aug 2006 12:32

Cash book from bank downloads
I started doing this from last April with two major banks

Where numerous small transactions and small number of debtor and creditor entries it saves alot of time in bank reconciliation

Life moves on!

Thanks (0)