I have imported data from a web query into an Excel worksheet. I am trying
to add the totals of a column (SUM). My formula in cell A10 is: =SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with an
additional space on the left side of the data. If I remove the space (delete
key), the SUM function will add just that cell. If I remove the 'space' from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but that
does not help. I've tried to "indent" or shift the entire column over, but
neither worked.

Anyone have a suggestion?

Thanks for your time.

Try this:

B1 = MID(A1,2,10) This will take up to 10 digit number, but in
text format
C1 = VALUE(B1) This will convert the text back into number

Now you can either SUM(C1:C9) or just copy and paste-special-value to
overwrite column A.


Hope it helps.



JoeD Wrote:
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with
an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the
'space' from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but
that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=377091

1)Edit one of the cells [F2]
2)Select the one blank character
3)Copy that character [Ctrl]+C
4)Select the range of "numbers"
5)EditReplace
-Replace what: Paste the character [Ctrl]+V
-Replace with: delete anything that might be there
-Click [Replace All]

That usually works for me.
--
Regards,
Ron

Try Highlighting the column and selecting Data Text to columns then
hitting the finish button in the pop up box

"JoeD" wrote in message
...
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the 'space'
from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.

JoeD,

Try:

=SUMPRODUCT(VALUE(TRIM(A1:A9)))

That may or may not work depending on what the actual lead ASCII character
is.

Alternatively, you could use

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

which should work no matter what the lead character is.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the 'space'
from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.

Bernie,

Thanks for the suggestion -- I used the

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1))) formula and it worked fine
until it hit a blank cell. I then received the #VALUE# error. Any
suggestions on how to get past blank cells? The range of data is around 155
rows within the column with several blank cells intermixed within the column.

Thanks in advance!

Joe


"Bernie Deitrick" wrote:

JoeD,

Try:

=SUMPRODUCT(VALUE(TRIM(A1:A9)))

That may or may not work depending on what the actual lead ASCII character
is.

Alternatively, you could use

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

which should work no matter what the lead character is.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the 'space'
from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.

Joe,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(A1:A9<"",VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)),0))

Of course, change all 3 of the A1:A9 references to your actual range.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
Bernie,

Thanks for the suggestion -- I used the

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1))) formula and it worked fine
until it hit a blank cell. I then received the #VALUE# error. Any
suggestions on how to get past blank cells? The range of data is around
155
rows within the column with several blank cells intermixed within the
column.

Thanks in advance!

Joe


"Bernie Deitrick" wrote:

JoeD,

Try:

=SUMPRODUCT(VALUE(TRIM(A1:A9)))

That may or may not work depending on what the actual lead ASCII
character
is.

Alternatively, you could use

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

which should work no matter what the lead character is.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with
an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the
'space'
from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but
that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.

Bernie,

THANKYOU!! Works Great!

JoeD

"Bernie Deitrick" wrote:

Joe,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(A1:A9<"",VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)),0))

Of course, change all 3 of the A1:A9 references to your actual range.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
Bernie,

Thanks for the suggestion -- I used the

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1))) formula and it worked fine
until it hit a blank cell. I then received the #VALUE# error. Any
suggestions on how to get past blank cells? The range of data is around
155
rows within the column with several blank cells intermixed within the
column.

Thanks in advance!

Joe


"Bernie Deitrick" wrote:

JoeD,

Try:

=SUMPRODUCT(VALUE(TRIM(A1:A9)))

That may or may not work depending on what the actual lead ASCII
character
is.

Alternatively, you could use

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

which should work no matter what the lead character is.

HTH,
Bernie
MS Excel MVP


"JoeD" wrote in message
...
I have imported data from a web query into an Excel worksheet. I am
trying
to add the totals of a column (SUM). My formula in cell A10 is:
=SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with
an
additional space on the left side of the data. If I remove the space
(delete
key), the SUM function will add just that cell. If I remove the
'space'
from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but
that
does not help. I've tried to "indent" or shift the entire column over,
but
neither worked.

Anyone have a suggestion?

Thanks for your time.