newbie: force paste as text?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KR - 20 Mar 2006 14:14 GMT

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

Signature

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.

Reply to this Message

Jim Rech - 20 Mar 2006 15:22 GMT

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

Signature

Jim

| Using XL2003...
|
[quoted text clipped - 25 lines]
| Many thanks for any assistance!
| Keith

Reply to this Message

KR - 20 Mar 2006 15:46 GMT

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!

> 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
[quoted text clipped - 47 lines]
> | thoughts, views, or policy of my employer. Any errors or omissions are my
> | own.

Reply to this Message

ward376 - 21 Mar 2006 07:55 GMT

Try something like this:
(Format general)

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

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

Reply to this Message