Excel won't recognize data

mietex

New Member
Joined
Jan 20, 2005
Messages
5
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).
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

New Member
Joined
Jan 20, 2005
Messages
5
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?   
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

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

New Member
Joined
Jan 20, 2005
Messages
5
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

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

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

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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.

(y)
 

mietex

New Member
Joined
Jan 20, 2005
Messages
5
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Oops sorry.

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

:oops:
 

Forum statistics

Threads
1,147,669
Messages
5,742,520
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top