Pad with leading zeros?

  • Thread starter Thread starter 43fan
  • Start date Start date
4

43fan

I have a string that's made up of concatenating other variables together,
one of which is a numerical value. I want to put leading zeros on the
numeric value, up to a full string length of 3.

Ex:
A = Y (string)
B = 1234 (string)
C = 1 (numeric)
A B C
Final string would look like this: Y1234.001

Help???

Thanks!
Shawn
 
Hi Shawn,

Is this what you are looking for, or are you looking for something
VBA-related?

=A1&A2&TEXT(A3/1000,".000")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Jake,

Thanks for the quick reply, but yes, something VBA related. I'm printing a
heading on a report that will be the concatenation of several fields like I
said, and one of those fields needs to have leading zeroes.

I'm wondering though, could I use something like this?

newvar = A + B + str(C/1000)?

C is always an integer, rarely goes higher than 4, occasionally to 6, but I
suppose could go as high as 15-20, but that would still end up with it being
..020 once divided by 1000. Would the str() function convert that to ".020"?

Gonna give it a try and see what happens. :) Thanks again!
Jake Marx said:
Hi Shawn,

Is this what you are looking for, or are you looking for something
VBA-related?

=A1&A2&TEXT(A3/1000,".000")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have a string that's made up of concatenating other variables
together, one of which is a numerical value. I want to put leading
zeros on the numeric value, up to a full string length of 3.

Ex:
A = Y (string)
B = 1234 (string)
C = 1 (numeric)
A B C
Final string would look like this: Y1234.001

Help???

Thanks!
Shawn
Click to expand...
Click to expand...
 
43fan said:
Thanks for the quick reply, but yes, something VBA related. I'm
printing a heading on a report that will be the concatenation of
several fields like I said, and one of those fields needs to have
leading zeroes.

I'm wondering though, could I use something like this?

newvar = A + B + str(C/1000)?
Click to expand...

When concatenating Strings, you should use the & operator. The Str function
will work, but Format gives you more control over the output.

As Mark suggested, you can use the Format function:

Dim sNewVar As String

sNewVar = Range("A1").Value & Range("A2").Value & _
Format$(Range("A3").Value/1000, ".000")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Jake,

I agree you should use & for annotation, but + works.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Jake Marx said:
43fan said:
Thanks for the quick reply, but yes, something VBA related. I'm
printing a heading on a report that will be the concatenation of
several fields like I said, and one of those fields needs to have
leading zeroes.

I'm wondering though, could I use something like this?

newvar = A + B + str(C/1000)?
Click to expand...

When concatenating Strings, you should use the & operator. The Str function
will work, but Format gives you more control over the output.

As Mark suggested, you can use the Format function:

Dim sNewVar As String

sNewVar = Range("A1").Value & Range("A2").Value & _
Format$(Range("A3").Value/1000, ".000")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
Click to expand...
 
Back
Top