Numbers want sum
Posted by Paul B on October 30, 2001 6:19 PM
I copied some information from my credit card statement online, but all the amounts have two spaces, or something, at the end of them. when I try to sum the column I get zero, I have tried to use replace with a space in the find field but it comes back as no match. I need a formula to remove the two spaces on the right, I can hit delete two times with the cursor at the end of the number and the sum function will work, there are about 400 lines so I dont want to do this one cell at a time. Thanks in advance
Posted by Richard S on October 30, 2001 6:42 PM
In the column next to your numbers, enter
=LEFT(A1,LEN(A1-2))*1
assuming your numbers start at row 1,and copy down as far as needed.
HTH
Richard
Posted by Paul B on October 31, 2001 2:38 AM
Richard, when I try it I get #value! If I type in a number 50.00 and two spaces after it the formula works, any other ideas? The amounts look like $50.00 $126.39 .all cells are formatted as general
Posted by Mark W. on October 31, 2001 6:40 AM
Try this instead...
Instead of a formula use Excel's Edit | Replace...
menu command. First, select your column of
numbers then replace all dollar signs ($) with "".
Next, replace all occurances of space (" ") with
"". This should eliminate your #VALUE! errors.
Posted by Paul B on October 31, 2001 7:58 AM
Need more Help
That removed the dollar sign and one space at the end, I still have to hit delete at the end of the number to get it to work, I don't know what it is at the end but replace does not think it is a space Instead of a formula use Excel's Edit | Replace...
Posted by Mark W. on October 31, 2001 8:50 AM
Re: Need more Help
What does =CODE(RIGHT(A1)) produce where A1
is a cell containing one of your troublesome
values? That removed the dollar sign and one space at the end, I still have to hit delete at the end of the number to get it to work, I don't know what it is at the end but replace does not think it is a space : Instead of a formula use Excel's Edit | Replace...
Posted by Paul B on October 31, 2001 9:14 AM
It produces 160
Posted by Mark W. on October 31, 2001 10:30 AM
Re: It produces 160
Okay, use Excel's Edit | Replace... menu command.
First, select your column of numbers then replace
all occurances of Alt+0160 with "". This is
accomplished by placing your cursor in the
Replace dialog's "Find what" field, holding down
the [ Alt ] key and pressing 0,1,6,0 on the
numeric key pad. You'll see the cursor move
1 position when you release the [ Alt ] key.
Leave the "Replace field" empty as before and
press [ Replace All ].
Posted by Paul B on October 31, 2001 10:53 AM
Re: It produces 160
Ok I selected the column I held down the alt key and typed 0,1,6,0 in find what, nothing went in, when I released the Alt key the cursor did not move, when I pressed replace all the replace box just closed. What am I doing wrong? Okay, use Excel's Edit | Replace... menu command.
Posted by Mark W. on October 31, 2001 11:25 AM
Re: It produces 160
> What am I doing wrong?
I don't know... works on my PC. Are you using
the numeric keypad?
Posted by Paul B on October 31, 2001 11:31 AM
Yes
Posted by Barrie Davidson on October 31, 2001 11:56 AM
My two cents worth.
How about using the VALUE function. For example, put this formula in the adjacent column.
=VALUE(A1)
Is this an option?Barrie Davidson
Posted by Juan Pablo on October 31, 2001 11:57 AM
Re: My two cents worth.
Continuing with Mark's advice, try this, in one of the trouble cells, select and copy that strange space at the end. Now, go to Replace, Paste in "Find What" and Replace All... that should take care of it
Juan Pablo :
Posted by Anon on October 31, 2001 12:03 PM
Re: It produces 160
Are you typing 0160 or 0,1,6,0 ?
It should be 0160
Posted by Aladin Akyurek on October 31, 2001 12:38 PM
Plus another two cents (Re: My two cents worth.)
Continuing with Mark's advice, try this, in one of the trouble cells, select and copy that strange space at the end. Now, go to Replace, Paste in "Find What" and Replace All... that should take care of it Juan Pablo
In B1 enter: =LEFT(A1,SUMPRODUCT(ISNUMBER(SEARCH({0,1,2,3,3,5,6,7,8,9},A1))+0))+0
Copy down as far as needed.
Aladin
======= : How about using the VALUE function. For example, put this formula in the adjacent column. : =VALUE(A1) : Is this an option?
Posted by Mark W. on October 31, 2001 12:39 PM
Re: It produces 160
A comma isn't on the numeric keypad. Are you typing 0160 or 0,1,6,0 ?
Posted by Mark W. on October 31, 2001 12:42 PM
Re: My two cents worth.
If A1 contains =1&CHAR(160) then =VALUE(A1)
returns the #VALUE! error. Gotta get read of
that control character.
Posted by Mark W. on October 31, 2001 12:46 PM
Paul, look a Juan's advice... it's a good alternative to Alt+0160 (nt)
Continuing with Mark's advice, try this, in one of the trouble cells, select and copy that strange space at the end. Now, go to Replace, Paste in "Find What" and Replace All... that should take care of it Juan Pablo : How about using the VALUE function. For example, put this formula in the adjacent column. : =VALUE(A1) : Is this an option?
Posted by Anon on October 31, 2001 12:57 PM
That's right. But he's doing something wrong, isn't he?
A comma isn't on the numeric keypad. : Are you typing 0160 or 0,1,6,0 ?
Posted by Anon on October 31, 2001 1:03 PM
Also ...
Paul B said that when he holds down Alt and types 0,1,6,0 nothing appears. So he must be inputting incorrectly.
Posted by Mark W. on October 31, 2001 1:09 PM
Yeah, I don't understand why it's not work'n for him... Did you see Juan's comment below (nt)
Posted by Paul B on October 31, 2001 1:25 PM
Re: Plus another two cents (Re: My two cents worth.)
Thanks to everyone for working on this for me the answer that Juan Pablo gave worked,what was that code 160?? now any ideas why I cant do the Alt 0,1,6,0 in the replace box? I will try it on my computer at work tomorrow and see if it works. Do you think reinstalling Excel might help? Again thanks to everyone for your help. Paul B
