How to add leading zero's to number to make char count = 6 ?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi,

All the cells in Column B in a worksheet must be 6 chars long. If I have a
string 123 in cell A1 using VBA how would I populate cell B2 with this value
including leading Zero to fill the gap to 6 characters. Column A could
contain a number with anything between 1 and 6 characters.

So for example the result should be

A B
1 123 000123
2 52 000052
3 52174 052174

Thanks

Paul
 
Hi Paul,
All the cells in Column B in a worksheet must be 6 chars long.
Click to expand...

Columns("B:B").NumberFormat = "000000"
If I have a string 123 in cell A1 using VBA how would I populate cell B2
with this value including leading Zero to fill the gap to 6 characters.
Click to expand...

To maintain a dynamic link, try:

Range("B2").FormulaR1C1 = "=RC[-1]"

O, to copy the static value, try:

Range("B2").Value = Range("A2").Value
 
Just use formulas

B1: = TEXT(A1,"000000")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob:

Is there a way of formatting the numbers to get the leading zeros and leave
them as numbers (not text)??
 
No, because as soon as you format to number, Excel will strip those leading
zeroes.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Yes, but that is just look of the data. From the OP, I read that as wanting
the actual string in B.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top