numbers not recognised as values

[nicko54]

Hi all,

I downloaded my bank statement into excel and the numbers are recognised as values. i.e: if i try and sum a column it comes up as zero???

i have attached a snapshot as an example...

any ideas?

[DonkeyOte]

My advice would be to use adjacent column(s) to clean up the data on a transaction by transaction basis, eg:

I3: =0+SUBSTITUTE(E3,CHAR(160),"")

You could if preferred do the aggregation using SUMPRODUCT but obviously you still don't have the numerics at transaction level eg:

E16: =SUMPRODUCT(--(SUBSTITUTE(E3:E14,CHAR(160),"")))

[sweep]

Hi, you can use this formula,

=VALUE(LEFT(E3,LEN(E3)-1))

there is a character at the end of the cell preventing excel reading it as a number.

[Cheeky Charlie]

Err...
Your download has come with non-breaking spaces after the numbers (to maintain the layout I suppose).
Select a cell with a number in
Select the last character (i.e. the space after the number)
Copy it (Ctrl+C)
open find & replace dialogue (Ctrl+H)
put cursor in "find" field
paste (Ctrl+V)
make sure "replace with" is blank
replace All

HTH

[nicko54]

copy and replace all - worked a dream!

many thanks

[hardeep.kanwar]

copy and replace all - worked a dream!

many thanks

The Last Character is Char 0160

Find what: ALT+0160
Replace With: <Blank>

Replace All

Ok


Note: While Entering 0160 in Find What HOLD the ALT Key

[DonkeyOte]

hardeep.kanwar, CC has already provided a solution here - note also that the ALT+0160 technique will only work on keyboards with a dedicated numeric keypad, CC's approach will work for all.

[martindwilson]

well you should be able to
Hold FN+ALT on most laptops without number pad and use the blue numbers on the letter keys on the keyboard

[DonkeyOte]

I stand well & truly corrected - thanks Martin that's great !