Can't work formulas in sheets copied from the net

[RickyDee]

POSTED EARLIER, HERE IS AN UPDATE:
First of all,
I posted the table on the web at
http://www.wiseplays.com/MLB_PROB.xls

NO, THIS IS DEFINITELY NOT an advertisement, rather only a
way to let people look at the table to see what I mean.
There definitely IS a space after the numbers in each
cell. =TRIM(cellref) didn't work, it only gave me a
number. Anything typed into this worksheet works, but not
what is copied from a table on the net.

Mr. Gord D. Sorry, but nothing worked. I get the feeling
that there may actually be 'nothing' in the cells at all.
Sort of like a transparency with overlays.

 

[Gord Dibben]

Ricky

I ran David McRitchie's TRIMALL macro on your data from the the workbook
MLB_PROB.xls

Data cleaned up nicely and became real numbers.

The spaces were most likely the 0160 character.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

See David's page for more explanation of the CHR 160

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

Gord Dibben Excel MVP

 

[Dave Peterson]

Try:
ctrl-A (to select the whole sheet)
Edit|replace
alt-0160 (from the numeric keypad)
with
(leave blank)

You have a non-breaking space after each value. Hit F2, then End and you'll go
to the end--and it isn't directly after the number.

You could use David McRitchie's Trimall macro to try to clean it up if you want
to use a macro.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

 

[Guest]

Thank you very much, guys. I'll try that soon as I can. Got a few other items to take care of first. I'll let you know soon as I try it. (hopefully, if I remember, due to old age setting in and all)

RickyDe

----- Gord Dibben wrote: ----

Rick

I ran David McRitchie's TRIMALL macro on your data from the the workboo
MLB_PROB.xl

Data cleaned up nicely and became real numbers

The spaces were most likely the 0160 character

Sub TrimALL(
'David McRitchie 2000-07-03 mod 2000-08-16 join.ht
Application.ScreenUpdating = Fals
Application.Calculation = xlCalculationManua
Dim cell As Rang
'Also Treat CHR 0160, as a space (CHR 032
Selection.Replace What:=Chr(160), Replacement:=Chr(32),
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=Fals
'Trim in Excel removes extra internal spaces, VBA does no
On Error Resume Next 'in case no text cells in selectio
For Each cell In Intersect(Selection,
Selection.SpecialCells(xlConstants, xlTextValues)
cell.Value = Application.Trim(cell.Value
Next cel
On Error GoTo
Application.Calculation = xlCalculationAutomati
Application.ScreenUpdating = Tru
End Su

See David's page for more explanation of the CHR 16

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

Gord Dibben Excel MV

On Sat, 1 May 2004 18:26:45 -0700, "RickyDee

 

[Guest]

Dave,
Sorry it took so long to respond back. I've been a bit busy here.
The TRIMALL macro worked outstanding. Thank you. This just shaved off about 45 minutes every morning, and gave me the chance to earn some Government pay.

Have a fine day,
R/
RickyDee