I have an Access database that via VB oode pumps data into a spreadsheet.
Some of the data looks like numbers, but in fact are billing codes, etc.
that need to be treated as text, not as numerics, so that the leading 0s are
preserved, e.g., "001765" should appear, not "1765". For the life of me I
cannot find a simple answer to this seemingly small problem. I have tried
everything I can find in the Excel/VB documentation and I always wind up with
"1765". If I had any hair to begin with, I would have torn it all out by
now. {Full tirade that would appear here has been omitted to preserve
space.} Any suggestions? Thanks!

If the numbers all have the same length, you can try the following whic
works on one cell. With a minor bit of modification you can make i
work on all of the returned data using a loop.

Range("A1").Select
Selection.NumberFormat = "@"
Dim I As Variant
I = Range("A1").Value
Select Case Len(I)
Case Is = 1: I = "00000" & I
Case Is = 2: I = "0000" & I
Case Is = 3: I = "000" & I
Case Is = 4: I = "00" & I
Case Is = 5: I = "0" & I
Case Else: I = I
End Select
Range("A1") =

--
Lauri
-----------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...fo&userid=2683
View this thread: http://www.excelforum.com/showthread.php?threadid=49185

Laurin:
Thank you very much. I will give it a try. Does this not seem like an
awful lot of trouble, though, to fix something that should be quite simple?
When you are in the spreadsheet, you can just go to Format Cells, select
Text, and then type in all the leading 0s you want. I'm rather astounded
that something I can do so easily in the interface seems programmatically to
require a detour through Siberia.
Peter
"Laurin" wrote:


If the numbers all have the same length, you can try the following which
works on one cell. With a minor bit of modification you can make it
work on all of the returned data using a loop.

Range("A1").Select
Selection.NumberFormat = "@"
Dim I As Variant
I = Range("A1").Value
Select Case Len(I)
Case Is = 1: I = "00000" & I
Case Is = 2: I = "0000" & I
Case Is = 3: I = "000" & I
Case Is = 4: I = "00" & I
Case Is = 5: I = "0" & I
Case Else: I = I
End Select
Range("A1") = I


--
Laurin
------------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...o&userid=26832
View this thread: http://www.excelforum.com/showthread...hreadid=491854

pbrase wrote:

I have an Access database that via VB oode pumps data into a spreadsheet.
Some of the data looks like numbers, but in fact are billing codes, etc.
that need to be treated as text, not as numerics, so that the leading 0s are
preserved, e.g., "001765" should appear, not "1765". For the life of me I
cannot find a simple answer to this seemingly small problem. I have tried
everything I can find in the Excel/VB documentation and I always wind up with
"1765". If I had any hair to begin with, I would have torn it all out by
now. {Full tirade that would appear here has been omitted to preserve
space.} Any suggestions? Thanks!

Have you tried making VB enter '001765?


--
Gordon Rainsford

London UK

Laurin:
Thank you very much. I will give it a try. Does this not seem like an
awful lot of trouble, though, to fix something that should be quit
simple?
When you are in the spreadsheet, you can just go to Format Cells
select
Text, and then type in all the leading 0s you want. I'm rathe
astounded
that something I can do so easily in the interface seem
programmatically to
require a detour through Siberia.
Peter

I agree that it does. I'm not very experienced in programming so ther
may be an easier way.

For the loop do this. First select the colum that you want to forma
and name the range Data. Then use the following:


Code
-------------------
Option Base 1

Sub Formatting()

Dim v as variant, Output() as string, I as Long
[Data].NumberFormat="@"
v = [Data]
Redim Output(ubound(v,1))
For I = 1 to ubound(v,1)
Select Case Len(v(I,1))
Case Is = 1: Output(I) = "00000" & v(I,1)
Case Is = 2: Output(I) = "0000" & v(I,1)
Case Is = 3: Output(I) = "000" & v(I,1)
Case Is = 4: Output(I) = "00" & v(I,1)
Case Is = 5: Output(I) = "0" & v(I,1)
Case Else: Output(I) = v(I,1)
End Select
Next I

[Data] = Application.worksheetfunction.transpose(Output)

End Su
-------------------

--
Lauri
-----------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...fo&userid=2683
View this thread: http://www.excelforum.com/showthread.php?threadid=49185

Incidentally, Laurin, you can probably get to the same place, instead of
using the Case statements, doing something like this:
Output = Right("000000" & InputValue, 6)
All the values are 6 digits long, so in this way you would get the string in
the right length with the appropriate number of 0s prepended. I use this
type of techique all the time in my Access code.

"Laurin" wrote:


Laurin:
Thank you very much. I will give it a try. Does this not seem like an
awful lot of trouble, though, to fix something that should be quite
simple?
When you are in the spreadsheet, you can just go to Format Cells,
select
Text, and then type in all the leading 0s you want. I'm rather
astounded
that something I can do so easily in the interface seems
programmatically to
require a detour through Siberia.
Peter

I agree that it does. I'm not very experienced in programming so there
may be an easier way.

For the loop do this. First select the colum that you want to format
and name the range Data. Then use the following:


Code:
--------------------
Option Base 1

Sub Formatting()

Dim v as variant, Output() as string, I as Long
[Data].NumberFormat="@"
v = [Data]
Redim Output(ubound(v,1))
For I = 1 to ubound(v,1)
Select Case Len(v(I,1))
Case Is = 1: Output(I) = "00000" & v(I,1)
Case Is = 2: Output(I) = "0000" & v(I,1)
Case Is = 3: Output(I) = "000" & v(I,1)
Case Is = 4: Output(I) = "00" & v(I,1)
Case Is = 5: Output(I) = "0" & v(I,1)
Case Else: Output(I) = v(I,1)
End Select
Next I

[Data] = Application.worksheetfunction.transpose(Output)

End Sub
--------------------


--
Laurin
------------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...o&userid=26832
View this thread: http://www.excelforum.com/showthread...hreadid=491854