Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force text to overlap | Setting up and Configuration of Excel | |||
excel save as csv - force text qualifier on every text field | Excel Discussion (Misc queries) | |||
newbie - how to paste into an occupied cell | New Users to Excel | |||
How to force a paste of values only? | Excel Programming | |||
Copy cells and paste to an address given in another cell (VBA Newbie) | Excel Programming |