# EXCEL can't sum cell with SPACE " "

S

#### 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 |
+-------------------------------------------------------------------+

R

#### Ron de Bruin

Hi

You can do this

Copy a empty cell
Right click on the selection
OK

K

#### 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.....................

D

#### 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.)

E

#### 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.

S

#### smd111

thank you all guys

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

G

#### Guest

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

Ron de Bruin said:
Hi

You can do this

Copy a empty cell