Converting a constant to a number

  • Thread starter Thread starter Longstrom
  • Start date Start date
L

Longstrom

Hi,

I'm downloading some statements online, and I'm copying
the the tables, and pasting the information into EXCEL
(2002).

The problem is that when I try to sum up the column, I get
a value of 0. I've determined that during my pasting of
the information, the information is being considered a
STRING or (in EXCEL terms) a constant.

I wanted to know if there was a way to convert the STRING
to a number so that I can add the numbers together. I
could re-enter the data into EXCEL manually, but there are
over 400 entries to be modified - I'm looking for a
simplier way of doing it.

FYI - I've tried playing around with FORMATS and with the
VALUE worksheet function. Neither of them have provided
me with results.

Any assistance would be greatly appreciated.
 
Hi
try the following:
- select an empty cell and copy this cell (e.g. with CTRL+C)
- now select your numbers (which are stored as text)
- goto 'Edit - Paste Special' and choose 'Add' as action
 
You could try

=SUM(VALUE(A1:A10))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
could you post some example data from your column. (plain text - no
attachment). If you have numbers in this range the procedure should
work
 
Hi,

I figured it out.

The value of the cell was actually " 50" for example.

I used the MID worksheet function to remove the space.

And I combined the MID with VALUE to get the numerical
value

Hence the final statement looked like the following:

=VALUE(MID(D1,2,3))

Thanks again for your assistance.
 
Back
Top