Removing spaces from imported data

[Randy Lefferts]

Excel 2003

I copied and pasted some "numerical" data from a table
into an excel worksheet. I needed to sum up several
columns worth of data but each time it enters the sum as
0.

So, after experimenting I found that there is a space
after each number. I tried using the TRIM and LEFT/RIGHT
functions and neither worked as far as allowing me to sum
up the columns.

When I retype the number, it obviously works. I tried
formatting the cells to numbers before and after trying
the TRIM function but to no avail.

Is there a way to get this data into a useable format and
if so, how? Any help is certainly appreciated!

Randy

 

[Frank Kabel]

Hi
try
=--SUBSTITUTE(A1," ","")

 

[Guest]

Hi Randy,
In addition to what you have already done, and what Frank has suggested
check out:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Trim spaces macro will pick up what CLEAN() and TRIM () miss
Also just to not overlook the obvious, make sure that they are not formatted
as text.

 

[Randy Lefferts]

Thank you for the reply Frank. Unfortunately it didn't
work but was worth a shot

 

[Randy Lefferts]

Thank you for the links. I will head over there and see
if it will work.