I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Ther are few was of doing this. You need to past text only. Try theses
suggestions. I gave you a lot of choices because I think some of the
formating you may want to keep and other you don't. Not sure which fomat
characters are in your data to give you only one option.

1) Copy the area again and do another paste. A square box with a clip board
will appear. Select different options options in this clip board.

2) copy the data into word and try the same thing with the clip board.
different options will appear in word than in excel. Then copy the data back
to excel.

3) this will always work, but you may loose the columns or rows. Copy the
data into Notepade. Then copy the data back to excel.


4) In word and excel on the Menu toolbar there is an option Paste Special.
Again try different options.
"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Enter and run this small macro:

Sub pete()
c1 = Chr(10)
c2 = Chr(13)
c3 = Chr(160)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlConstants)
v = r.Value
v = Replace(v, c1, "")
v = Replace(v, c2, "")
r.Value = Replace(v, c3, "")
Next
End Sub

--
Gary''s Student
gsnu200708


"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Forgive me Gary's student" but I have no clue what you are directing me to
do. I am a Neanderthal with the computer and the coding below doesn't tell
me where to do this code entering. Thanks for trying.

Pete

"Gary''s Student" wrote:

Enter and run this small macro:

Sub pete()
c1 = Chr(10)
c2 = Chr(13)
c3 = Chr(160)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlConstants)
v = r.Value
v = Replace(v, c1, "")
v = Replace(v, c2, "")
r.Value = Replace(v, c3, "")
Next
End Sub

--
Gary''s Student
gsnu200708


"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Tried what you suggested. Did 1, 2, and 4.......no luck. Where do I find
Notepad?

Pete



"Joel" wrote:

Ther are few was of doing this. You need to past text only. Try theses
suggestions. I gave you a lot of choices because I think some of the
formating you may want to keep and other you don't. Not sure which fomat
characters are in your data to give you only one option.

1) Copy the area again and do another paste. A square box with a clip board
will appear. Select different options options in this clip board.

2) copy the data into word and try the same thing with the clip board.
different options will appear in word than in excel. Then copy the data back
to excel.

3) this will always work, but you may loose the columns or rows. Copy the
data into Notepade. Then copy the data back to excel.


4) In word and excel on the Menu toolbar there is an option Paste Special.
Again try different options.
"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Macros are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary's Student
gsnu200708


"Pete" wrote:

Forgive me Gary's student" but I have no clue what you are directing me to
do. I am a Neanderthal with the computer and the coding below doesn't tell
me where to do this code entering. Thanks for trying.

Pete

"Gary''s Student" wrote:

Enter and run this small macro:

Sub pete()
c1 = Chr(10)
c2 = Chr(13)
c3 = Chr(160)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlConstants)
v = r.Value
v = Replace(v, c1, "")
v = Replace(v, c2, "")
r.Value = Replace(v, c3, "")
Next
End Sub

--
Gary''s Student
gsnu200708


"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

Sorry to report that your procedure didn't work. Damn!
Thanks for your efforts.

Pete

"Gary''s Student" wrote:


Macros are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary's Student
gsnu200708


"Pete" wrote:

Forgive me Gary's student" but I have no clue what you are directing me to
do. I am a Neanderthal with the computer and the coding below doesn't tell
me where to do this code entering. Thanks for trying.

Pete

"Gary''s Student" wrote:

Enter and run this small macro:

Sub pete()
c1 = Chr(10)
c2 = Chr(13)
c3 = Chr(160)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlConstants)
v = r.Value
v = Replace(v, c1, "")
v = Replace(v, c2, "")
r.Value = Replace(v, c3, "")
Next
End Sub

--
Gary''s Student
gsnu200708


"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

What happened when you tried it?

More details mean that you may get more suggestions.

Here's another suggestion:

Saved from a previous post...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Since you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")

Replace 13 with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Pete wrote:

Sorry to report that your procedure didn't work. Damn!
Thanks for your efforts.

Pete

"Gary''s Student" wrote:


Macros are very easy to install and use:

1. CNTRL-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary's Student
gsnu200708


"Pete" wrote:

Forgive me Gary's student" but I have no clue what you are directing me to
do. I am a Neanderthal with the computer and the coding below doesn't tell
me where to do this code entering. Thanks for trying.

Pete

"Gary''s Student" wrote:

Enter and run this small macro:

Sub pete()
c1 = Chr(10)
c2 = Chr(13)
c3 = Chr(160)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlConstants)
v = r.Value
v = Replace(v, c1, "")
v = Replace(v, c2, "")
r.Value = Replace(v, c3, "")
Next
End Sub

--
Gary''s Student
gsnu200708


"Pete" wrote:

I copied some golf data from espn.com and when I paste it into Excel the
formatting is full of old dos characters (little boxes) that I cannot remove
using the "clear formts" function in Excel. How do I get rid of the unwanted
formatting?

--

Dave Peterson