EXCEL can't sum cell with SPACE " "

[smd111]

I have imported data from some text file to do sum in EXCEL, as it is
always easy to SUM with EXCEL.

But here I surprise EXCEL can't do that simple sum,
B4 has value "339 " in cell seen as 339
B5 has value "2079 " in cell seen as 2079

Now i tried with =SUM(B4:B5), yield 0.000

I have checked what's wrong with in base cell, found excess space" “
after last digit.

I have billions of data to sum; I can't remove that space manually.

i am attaching that file for your ref.

i have also tried with TRIM() function but that also didn't work

Any function or any ideas to do this type of sum will be appreciated.


+-------------------------------------------------------------------+
|Filename: TEST FILE250920051.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3849 |
+-------------------------------------------------------------------+

 

[Ron de Bruin]

Hi

Your cells are text now
You can do this

Copy a empty cell
Select all your text numbers
Right click on the selection
Choose Paste Special...Add
OK

 

[Ken Wright]

Lots of ways to deal with that:-

Personally I keep a copy of Dave McRitchies 'Trimall' macro to hand and run
that against any imported data, and that usually clears up any issues like
that in a second:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards
Ken.....................

 

[Dave Peterson]

One more way...

You could just select that range and
edit|replace
what: (spacebar)
with: (leave blank)
replace all

(If it's really a space character.)

 

[Earl Kiosterud]

This array formula will sum values containing spaces:
=SUM(B4:B5+0)
Since it's an array formula, use Ctrl-Shift-Enter, not just Enter, when
you've finished editing it. The +0 causes it to coerce the text value to a
number for each value. I'm not sure that "coerce" is the correct term in
this case.

Or, since there are only two cells to be summed, you can use:
=B4+B5
Each individual reference (B4, etc) is coerced to a number.

 

[smd111]

thank you all guys

it's worked now...with help of Macro ----- TrimALL(

 

[Guest]

=&=sum(a6+a8+a9) if 2 cells are blank will not give value in one cell?

Hi

Your cells are text now
You can do this

Copy a empty cell
Select all your text numbers
Right click on the selection
Choose Paste Special...Add
OK