Cannot sum imported numbers even when using paste special add or multiply.

  • Thread starter Thread starter Shannon Pedersen
  • Start date Start date
S

Shannon Pedersen

I have a spreadsheet that contains imported numbers from a customized
web program - I'm not sure what they are using as their database. I
am unable to sum or add numbers in two of the columns. After checking
the newsgroups, I found several references to copying an empty cell,
then selecting the range of problem numbers and choosing Paste Special
Add. This did not solve the problem. I then also tried doing it
Click to expand...
where I copied a cell containing "1", then selecting the range of
cells and choosing Paste Special > Multiply. No go with this, either.

There is a space in the cell before each number, even after the Paste
Special, but removing it does not change anything. There are no label
indicators.

I have tried using the VALUE function and this doesn't help, either.
I'm pretty much at a loss as to what to do now.

Thanks,
Shannon
 
try the following macro - select the cells first and then run the macro.

Sub CellFix()
Dim cell As Range
For Each cell In Selection
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell

End Sub

if it doesn't work send me the excel file
 
Your data is garbage, or at least full of it <vbg>

Wing your way over to Dave McRitchie's Trimall Macro which will cure all known ills, including
being possessed by garbage. Stick a copy in your personal.xls for future use. It's one of those
must-have bits that I wouldn't be without, and it will clear out all rogue characters from
imported data.

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

When you have run it on your data, you may then need to copy an empty cell, select all your data
and do Edit / Paste Special / Add which will coerce it back to numeric. Then you can do as you
like with it.
 
Shannon - did you read my note?? I told you that was what was wrong with your data, and gave you
an excellent solution in the form of a ready written macro from Dave McRitchie called TrimAll -
Why reinvent the wheel?
 
just select the cells and do

Edit=>Replace
Find What: put in a space
Replace With: leave blank


then do replace all. No need to get a macro.

Regards,
Tom Ogilvy
 
Back
Top