pasting Word table info into Excel

When copying a table from Word (2000) into Excel (XP) it converts the numbers
to text so that formulas do not work. The Formula is fine, but the numbers
are seen as text. If I click on one of the number cells, then on the formula
bar, and at the beginning of the content hit backspace, it turns to a number
again, but there is NO SPACE there, must be some other hidden code or
something. I used to get a button that asked if I wanted to insert as text
or numbers, but a tech turned that function off and now it just pastes as
text. How can I get it to ask me again if I want to paste (from Word 2000)
numbers or text? Or better yet, just always paste numbers as numbers by
default?

Any solutions? Please help. Thank you!

David McRitchie has a macro that cleans up this kind of stuff:

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

If that doesn't work...

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

V-ger wrote:
>
> When copying a table from Word (2000) into Excel (XP) it converts the numbers
> to text so that formulas do not work. The Formula is fine, but the numbers
> are seen as text. If I click on one of the number cells, then on the formula
> bar, and at the beginning of the content hit backspace, it turns to a number
> again, but there is NO SPACE there, must be some other hidden code or
> something. I used to get a button that asked if I wanted to insert as text
> or numbers, but a tech turned that function off and now it just pastes as
> text. How can I get it to ask me again if I want to paste (from Word 2000)
> numbers or text? Or better yet, just always paste numbers as numbers by
> default?
>
> Any solutions? Please help. Thank you!

--

Dave Peterson

Wow. A lot of information that I don't understand, but it sounds like you
are very good at Excel. Might you happen to know where the setting is that
the tech turned off last week that made it quit asking me if I wanted to
paste the info in "text" or "numbers"? That would be easier - just to turn
this setting back on. I cannot find it. If not, I will try to use the macro
or add-ins but I hope I can just put it back the way it was last week, like
it originally came, without all of the extra stuff. And I really am grateful
for your assistance. Thanks for taking your time out for my issue - I
appreciate it.

"Dave Peterson" wrote:

> David McRitchie has a macro that cleans up this kind of stuff:
>
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> (look for "Sub Trimall()"
>
> If that doesn't work...
>
> 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
>
> V-ger wrote:
> >
> > When copying a table from Word (2000) into Excel (XP) it converts the numbers
> > to text so that formulas do not work. The Formula is fine, but the numbers
> > are seen as text. If I click on one of the number cells, then on the formula
> > bar, and at the beginning of the content hit backspace, it turns to a number
> > again, but there is NO SPACE there, must be some other hidden code or
> > something. I used to get a button that asked if I wanted to insert as text
> > or numbers, but a tech turned that function off and now it just pastes as
> > text. How can I get it to ask me again if I want to paste (from Word 2000)
> > numbers or text? Or better yet, just always paste numbers as numbers by
> > default?
> >
> > Any solutions? Please help. Thank you!
>
> --
>
> Dave Peterson
>

Maybe...

Tools|Options|Edit tab|
check the "show paste options buttons" checkbox.



V-ger wrote:
>
> Wow. A lot of information that I don't understand, but it sounds like you
> are very good at Excel. Might you happen to know where the setting is that
> the tech turned off last week that made it quit asking me if I wanted to
> paste the info in "text" or "numbers"? That would be easier - just to turn
> this setting back on. I cannot find it. If not, I will try to use the macro
> or add-ins but I hope I can just put it back the way it was last week, like
> it originally came, without all of the extra stuff. And I really am grateful
> for your assistance. Thanks for taking your time out for my issue - I
> appreciate it.
>
> "Dave Peterson" wrote:
>
> > David McRitchie has a macro that cleans up this kind of stuff:
> >
> > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> > (look for "Sub Trimall()"
> >
> > If that doesn't work...
> >
> > 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
> >
> > V-ger wrote:
> > >
> > > When copying a table from Word (2000) into Excel (XP) it converts the numbers
> > > to text so that formulas do not work. The Formula is fine, but the numbers
> > > are seen as text. If I click on one of the number cells, then on the formula
> > > bar, and at the beginning of the content hit backspace, it turns to a number
> > > again, but there is NO SPACE there, must be some other hidden code or
> > > something. I used to get a button that asked if I wanted to insert as text
> > > or numbers, but a tech turned that function off and now it just pastes as
> > > text. How can I get it to ask me again if I want to paste (from Word 2000)
> > > numbers or text? Or better yet, just always paste numbers as numbers by
> > > default?
> > >
> > > Any solutions? Please help. Thank you!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave, I appreciate the help, but I already tried that one... I guess it may
be a mystery since I can't ask the tech until next week. I will try one of
the add-ons that you suggested. I'm sure they will be helpful, and they are
always a great resource for me. Thanks for your patience. I really
appreciate it.

V-ger

"Dave Peterson" wrote:

> Maybe...
>
> Tools|Options|Edit tab|
> check the "show paste options buttons" checkbox.
>
>
>
> V-ger wrote:
> >
> > Wow. A lot of information that I don't understand, but it sounds like you
> > are very good at Excel. Might you happen to know where the setting is that
> > the tech turned off last week that made it quit asking me if I wanted to
> > paste the info in "text" or "numbers"? That would be easier - just to turn
> > this setting back on. I cannot find it. If not, I will try to use the macro
> > or add-ins but I hope I can just put it back the way it was last week, like
> > it originally came, without all of the extra stuff. And I really am grateful
> > for your assistance. Thanks for taking your time out for my issue - I
> > appreciate it.
> >
> > "Dave Peterson" wrote:
> >
> > > David McRitchie has a macro that cleans up this kind of stuff:
> > >
> > > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> > > (look for "Sub Trimall()"
> > >
> > > If that doesn't work...
> > >
> > > 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
> > >
> > > V-ger wrote:
> > > >
> > > > When copying a table from Word (2000) into Excel (XP) it converts the numbers
> > > > to text so that formulas do not work. The Formula is fine, but the numbers
> > > > are seen as text. If I click on one of the number cells, then on the formula
> > > > bar, and at the beginning of the content hit backspace, it turns to a number
> > > > again, but there is NO SPACE there, must be some other hidden code or
> > > > something. I used to get a button that asked if I wanted to insert as text
> > > > or numbers, but a tech turned that function off and now it just pastes as
> > > > text. How can I get it to ask me again if I want to paste (from Word 2000)
> > > > numbers or text? Or better yet, just always paste numbers as numbers by
> > > > default?
> > > >
> > > > Any solutions? Please help. Thank you!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>