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!

pasting Word table info into Excel
 

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

pasting Word table info into Excel
 

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

pasting Word table info into Excel
 

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

pasting Word table info into Excel
 

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