Excel won't recognize data

[mietex]

I coped a bunch of data from some Explorer pages (bank statements) into Excel and want to use the 'sum' function to total the amounts in a particular column, however Excel only recognizes *some* of the data. I copied it from 2 sets of pages, some reflecting Direct Deposits and some for Bank Checks (but otherwise completely identicle); Excel is only recognizing the data from the Bank Checks pages. The sum function will add the data from Bank Checks, but ignores the Direct Deposit data, and if I try to sum only Direct Deposit data, I get a VALUE# error message. Does anyone know what's going on and how I can get Excel to recognize ALL of the data? (I am not an Excel master, just an average user).

 

[Seti]

Welcome to the board.

My guess is that you have some unseen characters in the set of data that is not summing. Check to see if that is true. Use the LEN function or edit the cell and see if there appear to be any blanks. Then post back.

 

[mietex]

I used the LEN function for a value "$632.70" and it came back with a 9. For "$1096.30" I got a 10. I'm guessing that means you were right? Can you tell me how to fix this?   

 

[Todd Bardoni]

First try:-

=TRIM(CLEAN(A1))

 

[Seti]

You can try to TRIM the cells to get rid of any leading blanks, but I suspect that you have some CHAR(160)s in your data. This happens on data from the internet sometimes. At http://www.mvps.org/dmcritchie/excel/join.htm#trimall, there is a macro that gets rid on these things. Give it a try and see if that helps.

 

[mietex]

Okay, so now I've figured out there are 2 spaces after the numbers in the cells. Deleting these fixes the problem. (thank you so much for cueing me into that--I knew it had to be something ridiculously simple) Now, however, I have a new question: is there any way to delete these extra spaces en masse?

 

[Todd Bardoni]

Yes, see my formula above and copy down. Then copy the cells with the formula, Paste Special/Vlaues.

 

[Domski]

Alright fella,

A quick and easy way to get rid of spaces is to do a search replace (CTRL+H).

Put a space in the Find What and leave Replace With blank.

Take care though this will get rid of ALL the spaces in the either the worksheet or the cells you have highlighted.

Similarly if you know it's various occurences of two spaces put this in the Find What box.

 

[mietex]

Wow, thanks so much to all of you. I was about to throw the computer out the window. I think I can take it from here. Thanks again, so so much. Oh, and I'm not a fella--I'm a lady.

 

[Domski]

Oops sorry.

Must remember not to start all my replies with that. It's been a long day. Time to go home I think.