EXCEL can't sum cell with SPACE " "

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.

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"smd111" <[email protected]> wrote in message
news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> smd111
> ------------------------------------------------------------------------
> smd111's Profile: http://www.excelforum.com/member.php...o&userid=27508
> View this thread: http://www.excelforum.com/showthread...hreadid=470536
>

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


"smd111" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> smd111
> ------------------------------------------------------------------------
> smd111's Profile:
> http://www.excelforum.com/member.php...o&userid=27508
> View this thread: http://www.excelforum.com/showthread...hreadid=470536
>

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

smd111 wrote:
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> smd111
> ------------------------------------------------------------------------
> smd111's Profile: http://www.excelforum.com/member.php...o&userid=27508
> View this thread: http://www.excelforum.com/showthread...hreadid=470536

--

Dave Peterson

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.
--
Earl Kiosterud
www.smokeylake.com

"smd111" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> smd111
> ------------------------------------------------------------------------
> smd111's Profile:
> http://www.excelforum.com/member.php...o&userid=27508
> View this thread: http://www.excelforum.com/showthread...hreadid=470536
>

thank you all guys

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

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

"Ron de Bruin" wrote:

> 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
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "smd111" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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 |
> > +-------------------------------------------------------------------+
> >
> > --
> > smd111
> > ------------------------------------------------------------------------
> > smd111's Profile: http://www.excelforum.com/member.php...o&userid=27508
> > View this thread: http://www.excelforum.com/showthread...hreadid=470536
> >
>
>
>