Spacing in cells is preventing addition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I copied and pasted a spreadsheet to EXCEL 2003 and the cells have spaces in
front of the numbers preventing the addition function. How do I fix this?
 
I copied and pasted a spreadsheet to EXCEL 2003 and the cells have spaces in
front of the numbers preventing the addition function. How do I fix this?
Click to expand...

=--TRIM(A1)
=--SUBSTITUTE(TRIM(A1),CHAR(160),"")

CHAR(160) is a non-printing space frequently found in HTML documents.



--ron
 
I copied and pasted a spreadsheet to EXCEL 2003 and the cells have spaces in
front of the numbers preventing the addition function. How do I fix this?
Click to expand...

=--TRIM(A1)
=--SUBSTITUTE(TRIM(A1),CHAR(160),"")

CHAR(160) is a non-printing space frequently found in HTML documents.



--ron
 
Can you demonstrate,I tried and it is not working for me. Please see an
example below:

1,016.28 0.00
4,578.00 0.00
0.00 1,016.28
0.00 4,578.00
0.00 4,578.00
0.00 1,600.32
0.00 1,600.32

The grid did not copy: however, the above would be in cells.
 
Can you demonstrate,I tried and it is not working for me. Please see an
example below:

1,016.28 0.00
4,578.00 0.00
0.00 1,016.28
0.00 4,578.00
0.00 4,578.00
0.00 1,600.32
0.00 1,600.32

The grid did not copy: however, the above would be in cells.
 
Can you demonstrate,I tried and it is not working for me. Please see an
example below:

1,016.28 0.00
4,578.00 0.00
0.00 1,016.28
0.00 4,578.00
0.00 4,578.00
0.00 1,600.32
0.00 1,600.32

The grid did not copy: however, the above would be in cells.
Click to expand...

I don't see an example of what you did.

In general, those formulas will convert a string of numbers with leading or
following spaces into a real number. So if your values are in A1:B7, you could
put the formula into C1 and copy down to row 7, then across to column D. Those
results would be numbers that you could do arithmetic on.

For the information you posted, I merely pasted it into a worksheet and had no
problem with addition. It is already numeric.

When you write, "the above would be in cells", what do you mean? Is each value
in a separate cell? Or are there several values on one cell? If the latter,
which cell?


--ron
 
Can you demonstrate,I tried and it is not working for me. Please see an
example below:

1,016.28 0.00
4,578.00 0.00
0.00 1,016.28
0.00 4,578.00
0.00 4,578.00
0.00 1,600.32
0.00 1,600.32

The grid did not copy: however, the above would be in cells.
Click to expand...

I don't see an example of what you did.

In general, those formulas will convert a string of numbers with leading or
following spaces into a real number. So if your values are in A1:B7, you could
put the formula into C1 and copy down to row 7, then across to column D. Those
results would be numbers that you could do arithmetic on.

For the information you posted, I merely pasted it into a worksheet and had no
problem with addition. It is already numeric.

When you write, "the above would be in cells", what do you mean? Is each value
in a separate cell? Or are there several values on one cell? If the latter,
which cell?


--ron
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Displaying zeros in cells 2
Converting Text to Numbers in Excel w/ Additional Spaces 4
Excel Columns moving up and down on their own in a worksheet. What Goes? 2
want to track time while paste value in multiple column of same roe 1
trim cells with space in betwen numbers in cell 2
How to stop info from being pasted into a cell that has a drop dow 2
Preventing user input for individual cells 3
#N/A In Cells 7

Back
Top