newbie: force paste as text?
 

Using XL2003...

I have a source worksheet that has numbers stored as text, with leading
zeros.
I load that worksheet into an array for faster processing
I use a msgbox to confirm that I still have my full string values, including
leading zeros

but for everything I've tried, when I paste those values back into other
cells, they show up as numeric and I lose my leading zeros. The overall
length of the string, and the number of leading zeros can vary.

Sub pasteavalue()
Dim rets As String
rets = (Sheet4.Range("C6712").Value)
MsgBox rets ' shows 05315
Sheet4.Range("G6712").Value= rets ' pastes as numeric 5315
End Sub

I'm sure this must be simple, but a google search and help file search
hasn't turned up any solutions (maybe I'm missing a key search term or
something).

The problem is that I then use the pasted value to pull data from another
file that includes the leading zeros, and because of this situation, it
doesn't find any matches.

Many thanks for any assistance!
Keith


--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

newbie: force paste as text?
 

You use the term 'paste' but you're not pasting. Pasting by definition
follows a copy. What you're doing is no different than entering 0123 from
the keyboard. Excel drops leading zeros when that is done unless the cell
has the text number format. The fact that the source cell has text
formatting is irrelavant because you are not doing a copy/paste.

So...

Sheet4.Range("C6712").Copy Sheet4.Range("G6712")


or

With Sheet4.Range("G6712")
.NumberFormat = Sheet4.Range("C6712").NumberFormat
.Value = Sheet4.Range("C6712").Value
End With

--
Jim
"KR" wrote in message
...
| Using XL2003...
|
| I have a source worksheet that has numbers stored as text, with leading
| zeros.
| I load that worksheet into an array for faster processing
| I use a msgbox to confirm that I still have my full string values,
including
| leading zeros
|
| but for everything I've tried, when I paste those values back into other
| cells, they show up as numeric and I lose my leading zeros. The overall
| length of the string, and the number of leading zeros can vary.
|
| Sub pasteavalue()
| Dim rets As String
| rets = (Sheet4.Range("C6712").Value)
| MsgBox rets ' shows 05315
| Sheet4.Range("G6712").Value= rets ' pastes as numeric 5315
| End Sub
|
| I'm sure this must be simple, but a google search and help file search
| hasn't turned up any solutions (maybe I'm missing a key search term or
| something).
|
| The problem is that I then use the pasted value to pull data from another
| file that includes the leading zeros, and because of this situation, it
| doesn't find any matches.
|
| Many thanks for any assistance!
| Keith
|
|
| --
| The enclosed questions or comments are entirely mine and don't represent
the
| thoughts, views, or policy of my employer. Any errors or omissions are my
| own.
|
|

newbie: force paste as text?
 

My apologies for incorrect terminology. Also, my sample code was just for
demonstration purposes, but I'm actually using information that is already
in an array, so I don't have the luxury of copying a cell at a time.
However, if I understand correctly, all I need to do is force the format of
the destination cell to be "text" before setting that cell's value to my
array value... I'll go give that a try....
Thanks!

"Jim Rech" wrote in message
...
You use the term 'paste' but you're not pasting. Pasting by definition
follows a copy. What you're doing is no different than entering 0123 from
the keyboard. Excel drops leading zeros when that is done unless the cell
has the text number format. The fact that the source cell has text
formatting is irrelavant because you are not doing a copy/paste.

So...

Sheet4.Range("C6712").Copy Sheet4.Range("G6712")


or

With Sheet4.Range("G6712")
.NumberFormat = Sheet4.Range("C6712").NumberFormat
.Value = Sheet4.Range("C6712").Value
End With

--
Jim
"KR" wrote in message
...
| Using XL2003...
|
| I have a source worksheet that has numbers stored as text, with leading
| zeros.
| I load that worksheet into an array for faster processing
| I use a msgbox to confirm that I still have my full string values,
including
| leading zeros
|
| but for everything I've tried, when I paste those values back into other
| cells, they show up as numeric and I lose my leading zeros. The overall
| length of the string, and the number of leading zeros can vary.
|
| Sub pasteavalue()
| Dim rets As String
| rets = (Sheet4.Range("C6712").Value)
| MsgBox rets ' shows 05315
| Sheet4.Range("G6712").Value= rets ' pastes as numeric 5315
| End Sub
|
| I'm sure this must be simple, but a google search and help file search
| hasn't turned up any solutions (maybe I'm missing a key search term or
| something).
|
| The problem is that I then use the pasted value to pull data from
another
| file that includes the leading zeros, and because of this situation, it
| doesn't find any matches.
|
| Many thanks for any assistance!
| Keith
|
|
| --
| The enclosed questions or comments are entirely mine and don't represent
the
| thoughts, views, or policy of my employer. Any errors or omissions are
my
| own.
|
|

newbie: force paste as text?
 

Try something like this:
(Format general)

Range("yada!b2:b100") = Range("hooha!b2:b100").Formula

or convert your other file/table to numeric values...