Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba is converting text to values!
Need help with vba in Excel. I have a list of zip codes many of which begin
with zeroes so I have converted them to text using Excel's built-in Text function: Text(ZipCode,"00000"). When I run the vba command to assign the value of a zip to another cell on the spreadsheet, it converts it to a number, not as text, and thus my routine bombs on subsequent lines of code on a "type mismatch." ex: Range("NewZip").Value = Range("OldZip").Value where OldZip is the original zip code formatted as text and NewZip is the cell where the value of the zip code is assigned--with the leading zeroes stripped off. Is there some sort of Text command in vba whereby I could do something like: Range("NewZip").Value = Text(Range("OldZip).Value,"0000") you know, kind of like the built-in Excel text command? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba is converting text to values!
Very close.
Range("NewZip").Value = Format(Range("OldZip).Value,"00000") (I think you were missing a zero in your format.) -- Vasant "Bill_S" wrote in message ... Need help with vba in Excel. I have a list of zip codes many of which begin with zeroes so I have converted them to text using Excel's built-in Text function: Text(ZipCode,"00000"). When I run the vba command to assign the value of a zip to another cell on the spreadsheet, it converts it to a number, not as text, and thus my routine bombs on subsequent lines of code on a "type mismatch." ex: Range("NewZip").Value = Range("OldZip").Value where OldZip is the original zip code formatted as text and NewZip is the cell where the value of the zip code is assigned--with the leading zeroes stripped off. Is there some sort of Text command in vba whereby I could do something like: Range("NewZip").Value = Text(Range("OldZip).Value,"0000") you know, kind of like the built-in Excel text command? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba is converting text to values!
Range("NewZip").NumberFormat = "@" 'Set to TEXT format 1st
Range("NewZip").Value = Range("OldZip").Value "Bill_S" wrote in message ... Need help with vba in Excel. I have a list of zip codes many of which begin with zeroes so I have converted them to text using Excel's built-in Text function: Text(ZipCode,"00000"). When I run the vba command to assign the value of a zip to another cell on the spreadsheet, it converts it to a number, not as text, and thus my routine bombs on subsequent lines of code on a "type mismatch." ex: Range("NewZip").Value = Range("OldZip").Value where OldZip is the original zip code formatted as text and NewZip is the cell where the value of the zip code is assigned--with the leading zeroes stripped off. Is there some sort of Text command in vba whereby I could do something like: Range("NewZip").Value = Text(Range("OldZip).Value,"0000") you know, kind of like the built-in Excel text command? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text to numerical values | New Users to Excel | |||
Converting Text into Values | Excel Worksheet Functions | |||
Converting values to text | New Users to Excel | |||
Converting Numeric values to Text | Excel Worksheet Functions | |||
Converting Text Values to Dates | Excel Discussion (Misc queries) |